<?php

namespace App\Http\Controllers\Test;


use App\Http\Controllers\Student\OnlineTestController;
use App\Model\AbelArticle;
use App\Model\ArticleAbel;
use App\Models\Aalog;
use App\Models\Admin;
use App\Nanqi\Member;
use App\Nanqi\OperationLog;
use Dompdf\Dompdf;
use Egulias\EmailValidator\Validation\DNSCheckValidation;
use GuzzleHttp\Client;
use Hamcrest\Core\Set;
use Illuminate\Http\File;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Intervention\Image\ImageManagerStatic as Image;
use Mpdf\Mpdf;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Qiniu\Auth;
use Qiniu\Storage\UploadManager;
use Twilio\Rest\Client as Services_Twilio;

class IndexController extends Controller
{
    public function export3()
    {
        $names = ['绍兴宏奥', '杭州运通祥宝汽车销售服务有限公司', '杭州运通和晟汽车销售服务有限公司', '杭州运通和乔汽车销售服务有限公司', '宁波运通国奥汽车销售有限公司', '宁波运通宝晟汽车销售服务有限公司', '绍兴宏奥汽车销售服务有限公司', '金华运通汇宾汽车销售服务有限公司', '杭州祥宝', '杭州和晟', '杭州和乔', '宁波国奥', '宁波宝晟', '绍兴宏奥', '金华宾利/金华汇宾'];
        $names = ['宁波宝','宁宝宝晟','运通和乔','运通和晟','运通汇宾','运通祥宝'];

        $res = [];
        foreach ($names as $v) {
            $res[$v] = $this->export2($v);
            // $res[$v] = $this->export201($v);

        }
        return $res;
        // 浙江省金华市金东区东孝街道文定街188号金华运通汇宾汽车销售服务有限公司
    }

    public function export201($name)
    {
        if (empty($name)) {
            return 0;
        }
        $e = DB::connection('oldWarranty')->table('warranty')
            ->where('first_name', $name)
            ->count();
        return $e;
    }

    public function export203()
    {
        $names = [
            '杭州运通祥宝汽车销售服务有限公司',
            '杭州运通和晟汽车销售服务有限公司',
            '杭州运通和乔汽车销售服务有限公司',
            '宁波运通国奥汽车销售有限公司',
            '宁波运通宝晟汽车销售服务有限公司',
            '绍兴宏奥汽车销售服务有限公司',
            '金华运通汇宾汽车销售服务有限公司',
            '杭州祥宝',
            '杭州和晟',
            '杭州和乔',
            '宁波国奥',
            '宁波宝晟',
            '绍兴宏奥',
            '金华宾利/金华汇宾',
            '宁波宝',
            '宁宝宝晟',
            '运通和乔',
            '运通和晟',
            '运通汇宾',
            '运通祥宝',
        ];
        $names = [
            '宁波运通国奥汽车销售有限公司'
        ];
        $address = '浙江省金华市金东区东孝街道文定街188号金华运通汇宾汽车销售服务有限公司';
        //  or w.address='{$address}'

        $names = implode("','", $names);
        $query = <<<EOF
SELECT 
       w.warranty_id,
       w.first_name,
       c.region_name, 
       d.province_name, 
       e.city, 
       w.address, 
       w.phone_number, 
       u.unique_id, 
       u.company_name, 
       w.installer_name, 
       w.installation_date, 
       w.installation_price, 
       w.year_id,
       w.model_id,
       f.make_name, 
       w.vin_number, 
       w.license_plate,
       b.roll_number,
       g.part_name,
       h.film_type_name,
       b.length,
       b.width,
       w.additional_comments,
       if(b.precut_or_manual!=1, '手工', '裁切') as precut_or_manual

FROM warranty as w
LEFT JOIN users as u on w.user_id=u.user_id
LEFT JOIN warranty_to_vehicle_coverage as b on w.warranty_id=b.warranty_id
LEFT JOIN region_master as c  on c.region_id=w.region_id
LEFT JOIN province_master as d on d.province_id=w.province_id
LEFT JOIN cities as e on e.city_id=w.city
LEFT JOIN make_master as f ON f.make_id=w.make_id
LEFT JOIN part_master as g ON g.part_id=b.part_id
LEFT JOIN film_type_master as h ON h.film_type_id=b.film_type_id
WHERE (w.first_name in ('{$names}'))
and (c.language_id=3 OR c.language_id is null)
and (d.language_id=3 OR d.language_id is null)
AND (e.language_id=3 OR e.language_id is null)
AND (f.language_id=3 OR f.language_id is null)
AND (g.language_id=3 OR g.language_id is null)
AND (h.language_id=3 OR h.language_id is null)
ORDER BY w.warranty_id
EOF;

        $e = DB::connection('oldWarranty')->select($query);

        $res = [];
        foreach ($e as $k => $v) {
            $item = [];
            foreach ($v as $ak => $av) {
                $text = $av;
                if (mb_stripos($text, '&#')===0) {
                    /*$text = str_replace('&#', ';&#', $text);
                    $text = mb_substr($text, 1);*/
                    $text = html_entity_decode($text);
                }

                $item[$ak] = $text;
            }
            $res[] = $item;
        }
        $field = [
            'warranty_id',
            'first_name',
            'region_name',
            'province_name',
            'city',
            'address',
            'phone_number',
            'unique_id',
            'company_name',
            'installer_name',
            'installation_date',
            'installation_price',
            'year_id',
            'model_id',
            'make_name',
            'vin_number',
            'license_plate',
            'part_name',
            'precut_or_manual',
            'film_type_name',
            'roll_number',
            'length',
            'width',
            'additional_comments'
        ];
        $data = [];
        foreach ($res as $k => $v) {
            $item = [];
            foreach ($field as $f) {
                $item[] = [
                    'label' => $f,
                    'value' => $v[$f],
                ];
            }
            $data[] = $item;
        }

        $name = '质保导出-'.date('YmdHis');
        OfficeWork::makeExcel2($data, $name);
        return [];
    }

    public function export2($name)
    {
        /*if (empty($name)) {
            return 0;
        }
        $e = DB::connection('oldWarranty')->table('warranty')
            ->where('first_name', $name)
            ->count();
        return $e;*/
        $query = <<<EOF
SELECT 
w.warranty_id, w.first_name, c.region_name, d.province_name, e.city, w.address, w.phone_number, u.unique_id, u.company_name, w.installer_name, w.installation_date, w.installation_price, w.year_id,
w.model_id, f.make_name, w.vin_number, 
       w.license_plate,
       b.roll_number

FROM warranty as w
LEFT JOIN users as u on w.user_id=u.user_id
LEFT JOIN warranty_to_vehicle_coverage as b on w.warranty_id=b.warranty_id
LEFT JOIN region_master as c  on c.region_id=w.region_id
LEFT JOIN province_master as d on d.province_id=w.province_id
LEFT JOIN cities as e on e.city_id=w.city
LEFT JOIN make_master as f ON f.make_id=w.make_id
LEFT JOIN part_master as g ON g.part_id=b.part_id
LEFT JOIN film_type_master as h ON h.film_type_id=b.film_type_id
WHERE w.first_name='{$name}'
and (c.language_id=3 OR c.language_id is null)
and (d.language_id=3 OR d.language_id is null)
AND (e.language_id=3 OR e.language_id is null)
AND (f.language_id=3 OR f.language_id is null)
AND (g.language_id=3 OR g.language_id is null)
AND (h.language_id=3 OR h.language_id is null)
ORDER BY w.warranty_id
EOF;
        $e = DB::connection('oldWarranty')->select($query);

        $res = [];
        foreach ($e as $k => $v) {
            $item = [];
            foreach ($v as $ak => $av) {
                $text = $av;
                if (mb_stripos($text, '&#')===0) {
                    /*$text = str_replace('&#', ';&#', $text);
                    $text = mb_substr($text, 1);*/
                    $text = html_entity_decode($text);
                }

                $item[$ak] = $text;
            }
            $res[] = $item;
        }
        $field = [
            'warranty_id',
            'first_name',
            'region_name',
            'province_name',
            'city',
            'address',
            'phone_number',
            'unique_id',
            'company_name',
            'installer_name',
            'installation_date',
            'installation_price',
            'year_id',
            'model_id',
            'make_name',
            'vin_number',
            // 'license_plate',
            'roll_number'
        ];
        $data = [];
        foreach ($res as $k => $v) {
            $item = [];
            foreach ($field as $f) {
                $item[] = [
                    'label' => $f,
                    'value' => $v[$f],
                ];
            }
            $data[] = $item;
        }

        OfficeWork::makeExcel2($data, $name);
    }

    public function export101()
    {
        $name = '浙江省金华市金东区东孝街道文定街188号金华运通汇宾汽车销售服务有限公司';

        $query = <<<EOF
SELECT 
w.warranty_id, w.first_name, c.region_name, d.province_name, e.city, w.address, w.phone_number, u.unique_id, u.company_name, w.installer_name, w.installation_date, w.installation_price, w.year_id,
w.model_id, f.make_name, w.vin_number, 
       w.license_plate,
       b.roll_number

FROM warranty as w
LEFT JOIN users as u on w.user_id=u.user_id
LEFT JOIN warranty_to_vehicle_coverage as b on w.warranty_id=b.warranty_id
LEFT JOIN region_master as c  on c.region_id=w.region_id
LEFT JOIN province_master as d on d.province_id=w.province_id
LEFT JOIN cities as e on e.city_id=w.city
LEFT JOIN make_master as f ON f.make_id=w.make_id
WHERE w.address='{$name}'
and (c.language_id=3 OR c.language_id is null)
and (d.language_id=3 OR d.language_id is null)
AND (e.language_id=3 OR e.language_id is null)
AND (f.language_id=3 OR f.language_id is null)
ORDER BY w.warranty_id
EOF;
        $e = DB::connection('oldWarranty')->select($query);

        $res = [];
        foreach ($e as $k => $v) {
            $item = [];
            foreach ($v as $ak => $av) {
                $text = $av;
                if (mb_stripos($text, '&#')===0) {
                    /*$text = str_replace('&#', ';&#', $text);
                    $text = mb_substr($text, 1);*/
                    $text = html_entity_decode($text);
                }

                $item[$ak] = $text;
            }
            $res[] = $item;
        }
        $field = [
            'warranty_id',
            'first_name',
            'region_name',
            'province_name',
            'city',
            'address',
            'phone_number',
            'unique_id',
            'company_name',
            'installer_name',
            'installation_date',
            'installation_price',
            'year_id',
            'model_id',
            'make_name',
            'vin_number',
            // 'license_plate',
            'roll_number'
        ];
        $data = [];
        foreach ($res as $k => $v) {
            $item = [];
            foreach ($field as $f) {
                $item[] = [
                    'label' => $f,
                    'value' => $v[$f],
                ];
            }
            $data[] = $item;
        }

        return OfficeWork::makeExcel2($data, $name);
    }

    public function export1()
    {
        // $name = '绍兴宏奥';
        // $name = '杭州运通祥宝汽车销售服务有限公司';
        // $name = '杭州运通和晟汽车销售服务有限公司';
        // $name = '杭州运通和乔汽车销售服务有限公司';
        // $name = '宁波运通国奥汽车销售有限公司';
        // $name = '宁波运通宝晟汽车销售服务有限公司';
        // $name = '绍兴宏奥汽车销售服务有限公司';
        // $name = '金华运通汇宾汽车销售服务有限公司';
        // $name = '杭州祥宝';
        // $name = '杭州和晟';
        // $name = '杭州和乔';
        // $name = '宁波国奥';
        // $name = '宁波宝晟';
        // $name = '绍兴宏奥';
        // $name = '金华宾利/金华汇宾';
        $name = '运通和晟';

        $query = <<<EOF
SELECT 
w.warranty_id, w.first_name, c.region_name, d.province_name, e.city, w.address, w.phone_number, u.unique_id, u.company_name, w.installer_name, w.installation_date, w.installation_price, w.year_id,
w.model_id, f.make_name, w.vin_number, 
       w.license_plate,
       b.roll_number

FROM warranty as w
LEFT JOIN users as u on w.user_id=u.user_id
LEFT JOIN warranty_to_vehicle_coverage as b on w.warranty_id=b.warranty_id
LEFT JOIN region_master as c  on c.region_id=w.region_id
LEFT JOIN province_master as d on d.province_id=w.province_id
LEFT JOIN cities as e on e.city_id=w.city
LEFT JOIN make_master as f ON f.make_id=w.make_id
WHERE w.first_name='{$name}'
and (c.language_id=3 OR c.language_id is null)
and (d.language_id=3 OR d.language_id is null)
AND (e.language_id=3 OR e.language_id is null)
AND (f.language_id=3 OR f.language_id is null)
ORDER BY w.warranty_id
EOF;
        $e = DB::connection('oldWarranty')->select($query);

        $res = [];
        foreach ($e as $k => $v) {
            $item = [];
            foreach ($v as $ak => $av) {
                $text = $av;
                if (mb_stripos($text, '&#')===0) {
                    /*$text = str_replace('&#', ';&#', $text);
                    $text = mb_substr($text, 1);*/
                    $text = html_entity_decode($text);
                }

                $item[$ak] = $text;
            }
            $res[] = $item;
        }
        $field = [
            'warranty_id',
            'first_name',
            'region_name',
            'province_name',
            'city',
            'address',
            'phone_number',
            'unique_id',
            'company_name',
            'installer_name',
            'installation_date',
            'installation_price',
            'year_id',
            'model_id',
            'make_name',
            'vin_number',
            // 'license_plate',
            'roll_number'
        ];
        $data = [];
        foreach ($res as $k => $v) {
            $item = [];
            foreach ($field as $f) {
                $item[] = [
                    'label' => $f,
                    'value' => $v[$f],
                ];
            }
            $data[] = $item;
        }

        return OfficeWork::makeExcel2($data, $name);

        return [$res];
    }



    public function international_sms_send_demo()
    {
        $config = [
            'appid' => 'appid',
            'appkey' => 'appid',
            'sign_type' => 'normal',
            'server' => 'https://api.mysubmail.com/',
        ];

#        INTERNATIONALSMSend
        $e = new \INTERNATIONALSMSsend($config);
    }

    public function test120203()
    {
        $user = DB::connection('newWarranty')
            ->table('users')
            ->get('id');
        $e = Arr::pluck($user, 'id');
        $all = range(1, 2773);
        $d = array_diff($all, $e);
        return array_values($d);
    }

    public function test1202()
    {

        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 01:01:01';
        $id = '2750';
        $user_old = DB::connection('oldWarranty')
            ->table('users')
            ->where('user_id', $id)
            ->get();
        $array = ['id', 'creator_id', 'user_type', 'unique_id', 'company_name', 'abbr', 'first_name', 'last_name', 'username', 'email_address', 'password', 'address', 'city', 'province_id', 'zip', 'region_id', 'phone_number', 'note', 'cell_phone', 'wechat_id', 'date_signedup', 'additional_comment', 'province', 'status', 'mdtype', 'language_id', 'extension', 'created_at', 'updated_at', 'deleted_at'];
        $upload = [];
        foreach ($user_old as $k=>$v){
            $v->id = $v->user_id;
            $v->abbr = $v->company_name;
            $v->mdtype = 0;
            if ($v->date_signedup < $bdate) {
                $v->date_signedup = $bdate;
            }
            $v->created_at = $v->date_signedup;
            $v->updated_at = $v->date_signedup;
            if ($v->user_type == 2) {
                $v->deleted_at = $tdate;
            } else {
                $v->deleted_at = null;
            }
            $item = [];
            foreach ($array as $ak => $av) {
                $item[$av] = $v->$av;
            }
            $upload[] = $item;
        }
        foreach ($upload as $k =>$v) {
            $bool = DB::connection('newWarranty')
                ->table('users')
                ->where('id', $v['id'])
                ->update($v);
        }
        return [$upload[0]];
        $user = DB::connection('newWarranty')
            ->table('users')
            ->find($id);

        return [$user_old];
    }

    public function ex123(Request $request)
    {
        $ip = $request->ip();
        if (empty($ip)) {
            return abort(404);
        }

        $exp = explode('.', $ip);
        if (@$exp[0]!=10 || in_array(@$exp[1], [28,128])) {
            return abort(404);
        }

        return [$request->ip(), $exp];
        $t1 = [
            [
                'label' => '账号',
                'value' => 'CN00160',
            ],
            [
                'label' => '门店等级',
                'value' => '授权店',
            ],
            [
                'label' => '所属省代',
                'value' => 'CN00160',
            ],
            [
                'label' => '店面名称',
                'value' => 'CN00160',
            ],
            [
                'label' => '公司名称',
                'value' => 'CN00160',
            ],
            [
                'label' => '电话号码地区号',
                'value' => "测试\n测试\n测试\n测试\n测试",
            ],
        ];
        $data = [];
        foreach (range(0,2000) as $k => $v) {
            $data[] = $t1;
        }
        $filename = 'test';
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        if (empty($data)) {
        } else {
            $header = Arr::pluck($data[0], 'label');
            $sheet->fromArray($header, NULL, 'A1');
            foreach ($data as $k => $v) {
                $value = Arr::pluck($v, 'value');
                $sheet->fromArray($value, NULL, 'A'.(2+$k));
            }
            $length = count($data) + 1;
            $ls = range('A', 'Z');
            $nos = count($header);
            if ($nos<26) {
                $end = 'Z';
            } else {
                $f = floor($nos);
                $f = @$ls[$f];
                $end = $f.'Z';
            }
            $end = $end.$length;
            $spreadsheet->getActiveSheet()->getStyle('A1:'.$end)
                ->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle('A1');
        }
        $filename = $filename ?: time().'.xlsx';
        $filename = basename($filename, '.xlsx').'.xlsx';
        $temp_file = tempnam(sys_get_temp_dir(), 'PHPWord');
        $writer->save($temp_file);
        return response()->download($temp_file, $filename);
    }

    public function mytest2()
    {
        $bool = DB::table('aalog')->insertGetId(['new'=>json_encode([]),'old'=>json_encode([])]);
        return [$bool];
    }

    public function mytest()
    {
        // env()
        $user = session()->get('admin');

        return [$user];
        $user = DB::connection('newWarranty')->table('users')
            ->find(1);
        session(['admin'=>$user]);
        return [$user];
        // session(['admin'=>])

    }

    public function test5()
    {
        $roll_number = '';
        $user_id = '';
        $len2 = DB::table('precut_to_vehicle_coverage as tvc')
            ->leftJoin('precut as p', 'p.id', 'tvc.precut_kit_sale_id')
            ->where('tvc.roll_number', $roll_number)
            ->where('p.id', '>=', 1)
            ->where('p.created_by', $user_id)
            ->leftJoin('warranty as w', 'w.pre_id', 'p.id')
            ->whereNull('w.id')
            ->whereNull('w.deleted_at')
            ->whereNull('p.deleted_at')
            ->whereNull('tvc.deleted_at');
        // ->sum('length');
        return [$len2->toSql()];
    }

    public function log4()
    {
        $file = __DIR__.'/laravel-2020-11-19.log';
        $bool = is_file($file);
        return [$bool];
    }

    /*
     * 剩余长度*/
    public function up5()
    {
        $rno = []; // ID 卷号对应数组
        $rnl = [];  // 卷号对应长度
        $rnid = [];

        $rnu = []; // [1=>1500, 15=>0,

        $rst = []; // 用户#卷号=长度
        // 主表
        $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->select(['roll_id', 'roll_number', 'length'])
            ->orderBy('roll_id')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = trim($v->roll_number);
            $rno[$v->roll_id] = $roll_number;
            $rnl[$roll_number] = $v->length;
            $rnu[$roll_number] = [
                1 => $v->length
            ];
        }
        // 销售表，最后一个人拥有全部
        $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $rnu[$roll_number] = [
                $v->sold_to_user_id => 0,
                $v->sold_by_user_id => $rnl[$roll_number]
            ];
        }
        // 质保表，获取全部，套件表仅获取未安装
        $pdu = []; // 套件ID对应用户ID
        $p =  DB::connection('oldWarranty')
            ->table('precut_kit as w')
            ->get(['precut_autoincrement_id', 'user_id']);
        foreach ($p as $k=>$v) {
            $pdu[$v->precut_autoincrement_id] = $v->user_id;
        }

        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'w.warranty_id', 'wvc.warranty_id')
            ->select(['w.pre_id', 'w.user_id', 'w.pre_id', 'wvc.vehicle_coverage_id', 'wvc.warranty_id', 'wvc.roll_number', 'wvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (!empty($v->pre_id)) {
                $user_id = @$pdu[$v->pre_id] ?: $v->user_id;
            }
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut_kit as p', 'p.precut_autoincrement_id', 'pvc.precut_kit_sale_id')
            ->where('p.installed', '!=', 'Yes')
            ->select(['p.precut_autoincrement_id', 'p.user_id', 'pvc.vehicle_coverage_id', 'pvc.precut_kit_sale_id', 'pvc.roll_number', 'pvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        /*$wids  = DB::connection('oldWarranty')
            ->table('warranty as w')
            ->where('w.pre_id', '>=', 1)
            ->get(['pre_id']);
        $wids = Arr::pluck($wids, 'pre_id');
        $wids = array_unique($wids);
        $p =  DB::connection('oldWarranty')
            ->table('precut_kit as w')
            ->get('precut_autoincrement_id');
        $pids = Arr::pluck($p, 'precut_autoincrement_id');
        return [count($pids), array_diff($wids, $pids)];*/
        // asort($rst);
        $upload = [];

        $tdate = date('Y-m-d H:i:s');
        foreach ($rst as $k => $v) {
            $ar = explode('#', $k, 2);
            $item = [
                'user_id' => $ar[0],
                'roll_number' => $ar[1],
                'restlen' => $v,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";

        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);

        return [count($rst)];

        $count = DB::connection('oldWarranty')
            ->table('precut_kit')
            ->count();

        return [count($data), $count, $count2];

    }


    public function ttt1()
    {
        $file = __DIR__.'/Controllers.xlsx';
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $a1 = $sheet->getCell('A1')->getValue(); // 安装日期
        $a2 = $sheet->getCell('A2')->getValue(); // 安装日期
        $a3 = $sheet->getCell('A3')->getValue(); // 安装日期
        $a4 = $sheet->getCell('A4')->getValue(); // 安装日期
        $a5 = $sheet->getCell('A5')->getValue(); // 安装日期
        $a3 = $a5;
        try {
            if (is_string($a3)) {
                $installation_date = strtotime($a3);
            } else {
                $installation_date = Date::excelToTimestamp($a3);
            }
        } catch (\Exception $exception) {
            $installation_date = 0;
        }

        $e = '';
        // $e = Date::dateTimeToExcel($a1);

        return [$a1, $a2, $a3, $e, is_string($a2), $installation_date, date('Y-m-d', $installation_date)];
    }

    protected function parseWarrantyExcelFile($file)
    {
        if (!file_exists($file)) {
            abort(404);
        }

        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $first_name = $sheet->getCell('A4')->getValue();
        $region = $sheet->getCell('B4')->getValue(); // 获取id
        $province = $sheet->getCell('C4')->getValue(); // 获取id
        $city = $sheet->getCell('D4')->getValue(); // 获取id
        $zip = $sheet->getCell('E4')->getValue();
        $email_address = $sheet->getCell('F4')->getValue();
        $phone_number_2 = $sheet->getCell('G4')->getValue(); // 获取电话与extension 原文，-分割
        $address = $sheet->getCell('B5')->getValue();
        $year_id = $sheet->getCell('A8')->getValue();
        $make_name = $sheet->getCell('B8')->getValue(); // 获取make_id
        $model_name = $sheet->getCell('C8')->getValue(); // 使用原文输入

        $license_plate = $sheet->getCell('E8')->getValue(); // 车牌
        $vin_number = $sheet->getCell('F8')->getValue(); // 车架号

        $qudao = $sheet->getCell('A11')->getValue(); // 安装师傅
        $installer_name = $sheet->getCell('B11')->getValue(); // 安装师傅
        $installation_date = $sheet->getCell('C11')->getValue(); // 安装日期
        $installation_price = $sheet->getCell('E11')->getValue(); // 安装价格

        $installation_images_zip_url = $spreadsheet->getActiveSheet()->getCell('G11')->getHyperlink()->getUrl(); // 安装图片地址
        // $installation_images = $this->getInstalationImagesByZipUrl($installation_images_zip_url);

        $msg = [];

        /*$qudao_id = getUserIdByName($qudao) ?: 0;
        if (empty($qudao_id)) {
            $msg[] = "门店或安装渠道不存在：" . $qudao;
        }

        $region_id = $this->getColumnIdByName('region_master', $region);
        if (empty($region_id)) {
            $msg[] = "地区不存在：" . $region;
        }
        $province_id = $this->getColumnIdByName('province_master', $province);
        if (empty($province_id)) {
            $msg[] = "省不存在：" . $province;
        }
        $city_id = $this->getColumnIdByName('cities', $city);
        if (empty($city_id)) {
            $msg[] = "城市不存在：" . $city;
        }
        $pns = explode('-', $phone_number_2);
        if (count($pns) != 2) {
            $msg[] = "电话号码有误，区号或电话号码不完整：" . $phone_number_2;
        }*/

        $vehicles = [];
        $row_no = 14;
        $max_row = 30;
        $n = '';
        while ($row_no<=$max_row && $n != '补充说明'){
            $item = [];
            $part = $sheet->getCell('A'.$row_no)->getValue(); // 套件
            $precut_or_manual = $sheet->getCell('B'.$row_no)->getValue(); // 裁切或手工
            $roll_number = $sheet->getCell('C'.$row_no)->getValue(); // 卷号
            $length = $sheet->getCell('E'.$row_no)->getValue(); // 使用长度
            $film_type_name = $sheet->getCell('F'.$row_no)->getValue(); // 膜的种类
            $width = $sheet->getCell('G'.$row_no)->getValue(); // 薄膜宽度
            $part = trim($part);
            if (empty($part) || $part == '补充说明') {
                $n = '补充说明';
            } else {
                /*$length_ = (int) $length;
                $width_ = (int) $width;
                $part_id = $this->getColumnIdByName('part_master', $part);
                if (empty($part_id)) {
                    $msg[] = "安装部位不存在：" . $part; // 第 {$row_no} 行
                }
                $rne = cRNe($roll_number);
                if (empty($rne)) {
                    $msg[] = "使用卷号不存在：" . $roll_number;
                }
                if (empty($length_)) {
                    $msg[] = "使用长度为0或非数字：" . $length;
                }
                $film_type_id = $this->getColumnIdByName('film_type_master', $film_type_name);
                if (empty($film_type_id)) {
                    $msg[] = "膜的种类不存在：" . $film_type_name;
                }*/

                $item = [
                    'precut_or_manual' => $precut_or_manual == '手工' ? 2 : 1,
                    'part_id' => $part,
                    'roll_number' => $roll_number,
                    'length' => $length,
                    'width' => $width,
                    'film_type_id' => $film_type_name,
                ];
                $vehicles[] = $item;
            }
            $row_no++;
        }
        return [$vehicles];

        $additional_comments = $sheet->getCell('A'.$row_no)->getValue();

        $user_id = session('admin.id');
        $model = new Warranty();
        $model->user_id = $qudao_id;
        $model->first_name = trim($first_name);
        $model->address = trim($address);
        $model->city = $city_id;
        $model->province_id = $province_id;
        $model->zip = trim($zip);
        $model->region_id = $region_id;
        $model->email_address = trim($email_address);
        $model->phone_number = trim(@$pns[1]);
        $model->extension = trim(@$pns[0]);
        $model->installer_name = trim($installer_name);
        $model->installation_date = trim($installation_date);
        $model->installation_price = trim($installation_price);

        $model->year_id = trim($year_id);
        $model->make_id = $make_id;
        $model->model_id = $model_id;
        $model->license_plate = trim($license_plate);
        $model->vin_number = trim($vin_number);
        $model->additional_comments = trim($additional_comments);

        $model->created_by = $admin_id;
        $model->warranty_id = makeWarrantyId($model->installation_date);
        $model->approved = 3;

        $model->save();

        $dtime = gDate();
        /*
         * 此处不再判断提交数据是否合法*/
        $uploads = [];
        foreach ($vehicles as $vk => $vv) {
            $vv['warranty_id'] = $model->id;
            $vv['created_at'] = $dtime;
            $vv['updated_at'] = $dtime;
            $uploads[] = $vv;
        }
        DB::table('warranty_to_vehicle_coverage')->insert($uploads);

        $imgs = [];
        $rmg = $installation_images;
        foreach ($rmg as $k => $v) {
            $item = [];
            $item['warranty_id'] = $model->id;
            $item['created_at'] = $dtime;
            $item['updated_at'] = $dtime;
            $item['image_src'] = $v;
            $item['part_id'] = $k+1;
            $imgs[] = $item;
        }
        if ($imgs) {
            DB::table('warranty_image')->insert($imgs);
        }
        $alogs = alogs('质保', '添加', $model->id, mmc($model, $uploads, $imgs), null);
        $msgModel = new WarrantyMsg();
        $msgModel->content = json_encode($msg);
        $msgModel->warranty_id = $model->id;
        $msgModel->status = 0;
        $msgModel->save();

        return $model;
    }


    public function addUser()
    {
        $admin_id = 2;
        if ($admin_id == 1) {
            $u = [
                5 => [
                    'Christine',
                    makePassword(),
                    15022216522,
                ],
                6 => [
                    '文文',
                    makePassword(),
                    18526871010,
                ],
                7 => [
                    '向辉',
                    makePassword(),
                    13917780090,
                ],
            ];
            foreach ($u as $k => $v) {
                $model = Admin::findOrNew($k);
                $model->id = $k;
                $name = $v[0];
                $password = $v[1];
                $phone_number = $v[2];
                $model->username = $name;
                $model->abbr = $name;
                $model->unique_id = $name;
                $model->company_name = $name;
                $model->first_name = $name;
                $model->user_type = 1;
                $model->status = 1;
                $model->phone_number = $phone_number;
                $model->password = bcrypt($password);
                $model->save();
            }
            return [$u];
        }
    }


    /*
     * 更新序号
     * 套件由precut_kit改为precut*/
    private function initSebquence()
    {
        // table => sequences
        // column   => precut
    }
    public function up123()
    {
        $data = DB::connection('mysql123')->table('product_types')->get();
        foreach ($data as $k => $v) {
            $model = ProductTypes::withTrashed()->findOrNew($v->id);
            $model->id = $v->id;
            $model->english_value = $v->english_value;
            $model->traditional_chiness_value = $v->traditional_chiness_value;
            $model->simplified_chiness_value = $v->simplified_chiness_value;
            $model->created_at = $v->created_at;
            $model->updated_at = $v->updated_at;
            $model->deleted_at = $v->deleted_at;
            $model->save();
        }
    }

    /*更新权限*/
    private function initRoles()
    {
        $data = DB::connection('mysql123')->table('roles')->get();
        foreach ($data as $k => $v) {
            $model = Roles::withTrashed()->findOrNew($v->id);
            $model->id = $v->id;
            $model->name = $v->name;
            $model->created_at = $v->created_at;
            $model->created_by = $v->name;
            $model->deleted_at = $v->deleted_at;
            $model->edit_able = $v->edit_able;
            $model->status = $v->status;
            $model->updated_at = $v->updated_at;
            $model->save();
        }
        $data = DB::connection('mysql123')->table('permissions')->get();
        foreach ($data as $k => $v) {
            $model = Permissions::withTrashed()->findOrNew($v->id);
            $model->id = $v->id;
            $model->english_value = $v->english_value;
            $model->traditional_chiness_value = $v->traditional_chiness_value;
            $model->simplified_chiness_value = $v->simplified_chiness_value;
            $model->parent_id = $v->parent_id;
            $model->type = $v->type;
            $model->url = $v->url;
            $model->page_name = $v->page_name;
            $model->icon = $v->icon;
            $model->controller = $v->controller;
            $model->methods = $v->methods;
            $model->created_at = $v->created_at;
            $model->updated_at = $v->updated_at;
            $model->deleted_at = $v->deleted_at;
            $model->save();
        }
        return $data;
    }

    /*
     * 初始化更新省代*/
    private function sd_sync1()
    {
        $datas = UserSync::where('unique_id', '!=', '')->get();
        $upd = [];
        foreach ($datas as $k => $v) {
            $key = strtoupper(trim($v->unique_id));
            $upd[$key] = $v;
        }
        $datas2 = Admin::withTrashed()->where('user_type',2)->get(['id', 'unique_id']);
        foreach ($datas2 as $k => $v) {
            $model = Admin::withTrashed()->find($v->id);
            $len = strlen($model->password);
            if ($len<32) {
                $model->password = bcrypt($model->password);
            }
            $key = strtoupper(trim($model->unique_id));
            // $model->unique_id = $key;
            if (empty($upd[$key])) {
                $model->status = 0;
                $model->save();
                $model->delete();
            } else {
                $m2 = $upd[$key];
                $obj = json_decode($m2->datas);
                $model->abbr = $obj->abbr;
                $model->company_name = $obj->company_name;
                $model->save();
            }
        }
        foreach (['CN00017','CN000019'] as $v) {
            $model = Admin::withTrashed()->where('unique_id', $v)->first();
            if (empty($upd[$key])) {
                continue ;
            }
            $m2 = $upd[$key];
            $obj = json_decode($m2->datas);
            if (empty($model->id)) {
                $model = new Admin();
                $arr = [
                    'F' => 'extension',
                    'G' => 'phone_number',
                    'H' => 'email_address',
                    'I' => 'status',
                    'J' => 'region_id',
                    'K' => 'province_id',
                    'L' => 'city',
                    'M' => 'address',
                    'N' => 'first_name',
                    'O' => 'cell_phone',
                    'P' => 'wechat_id',
                ];
                foreach ($arr as $ar) {
                    $model->$ar = $obj->$ar;
                }
                $model->password = bcrypt(makePassword());
            }
            $model->unique_id = $v;
            $model->status = 1;
            $model->abbr = $obj->abbr;
            $len = strlen($model->password);
            if ($len<32) {
                $model->password = bcrypt($model->password);
            }
            $model->company_name = $obj->company_name;
            $model->save();
        }
        return [$upd, $datas2];
    }
    /*
     * 初始化更新经销商*/
    private function jxs_sync1()
    {
        $datas = UserSync::where('unique_id', '!=', '')->get();
        $upd = [];
        foreach ($datas as $k => $v) {
            $key = strtoupper(trim($v->unique_id));
            $upd[$key] = $v;
        }
        $datas2 = Admin::withTrashed()->where('user_type',3)->get(['id', 'unique_id']);
        foreach ($datas2 as $k => $v) {
            $model = Admin::withTrashed()->find($v->id);
            $key = strtoupper(trim($model->unique_id));
            // $model->unique_id = $key;
            if (empty($upd[$key])) {
                $model->status = 0;
                $model->save();
                $model->delete();
            } else {
                $m2 = $upd[$key];
                $obj = json_decode($m2->datas);
                $model->status = $obj->status;
                $model->abbr = $obj->abbr;
                $model->company_name = $obj->abbr;
                $model->mdtype = $obj->mdtype;
                $model->region_id = $obj->region_id;
                $model->province_id = $obj->province_id;
                $model->city = $obj->city;
                $model->address = $obj->address;
                $model->save();
            }
        }
        return [$upd, $datas2];
    }

    public function wtUser2()
    {
        $admin = [
            'admin' => 'G8vjxrdDQwNM1IZK',
            'kiwili' => 'ig6XLxFCghmrJZYP',
            'yangyue' => 'S8eLu731nrUnFHdw',
            'wenwen' => 'JSS4HpeAlM7hIOQk',
        ];
        $admin = Admin::find(1);
        $admin->password = bcrypt('G8vjxrdDQwNM1IZK');
        $admin->save();
        $admin = Admin::find(2);
        $admin->password = bcrypt('ig6XLxFCghmrJZYP');
        $admin->save();
        $admin = Admin::find(3);
        $admin->password = bcrypt('S8eLu731nrUnFHdw');
        $admin->save();
        $admin = Admin::find(4);
        $admin->password = bcrypt('JSS4HpeAlM7hIOQk');
        $admin->save();

    }


    public function wtUsers()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $data = DB::connection('oldWarranty')
            ->table('users')
            ->orderBy('user_id')
            ->get();
        $array = ['id', 'creator_id', 'user_type', 'unique_id', 'company_name', 'abbr', 'first_name', 'last_name', 'username', 'email_address', 'password', 'address', 'city', 'province_id', 'zip', 'region_id', 'phone_number', 'note', 'cell_phone', 'wechat_id', 'date_signedup', 'additional_comment', 'province', 'status', 'mdtype', 'language_id', 'extension', 'created_at', 'updated_at'];
        $upload = [];

        foreach ($data as $k=>$v){
            $v->id = $v->user_id;
            $v->mdtype = 0;
            $v->abbr = $v->company_name;
            $v->username = $v->company_name;
            $v->unique_id = strtoupper(trim($v->unique_id));
            // $v->password = bcrypt(trim($v->password));
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO users (`id`, `creator_id`, `user_type`, `unique_id`, `company_name`, `abbr`, `first_name`, `last_name`, `username`, `email_address`, `password`, `address`, `city`, `province_id`, `zip`, `region_id`, `phone_number`, `note`, `cell_phone`, `wechat_id`, `date_signedup`, `additional_comment`, `province`, `status`, `mdtype`, `language_id`, `extension`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";

        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);
        return [$data[0]];

    }


    /*
     * 剩余长度*/
    public function up4()
    {
        $rno = []; // ID 卷号对应数组
        $rnl = [];  // 卷号对应长度
        $rnid = [];

        $rst = []; // 用户#卷号=长度
        // 主表
        $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->select(['roll_id', 'roll_number', 'length'])
            ->orderBy('roll_id')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = trim($v->roll_number);
            $key = '1#'.$roll_number;
            $rst[$key] = $v->length;
            $rno[$v->roll_id] = $roll_number;
            $rnl[$roll_number] = $v->length;
        }
        // 销售表，最后一个人拥有全部
        $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $key = $v->sold_to_user_id.'#'.$roll_number;
            $rst[$key] = 0;
            $key = $v->sold_by_user_id.'#'.$roll_number;
            $rst[$key] = $rnl[$roll_number];
        }
        // 质保表，获取全部，套件表仅获取未安装
        $pdu = []; // 套件ID对应用户ID
        $p =  DB::connection('oldWarranty')
            ->table('precut_kit as w')
            ->get(['precut_autoincrement_id', 'user_id']);
        foreach ($p as $k=>$v) {
            $pdu[$v->precut_autoincrement_id] = $v->user_id;
        }

        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'w.warranty_id', 'wvc.warranty_id')
            ->select(['w.pre_id', 'w.user_id', 'w.pre_id', 'wvc.vehicle_coverage_id', 'wvc.warranty_id', 'wvc.roll_number', 'wvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (!empty($v->pre_id)) {
                $user_id = @$pdu[$v->pre_id] ?: $v->user_id;
            }
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut_kit as p', 'p.precut_autoincrement_id', 'pvc.precut_kit_sale_id')
            ->where('p.installed', '!=', 'Yes')
            ->select(['p.precut_autoincrement_id', 'p.user_id', 'pvc.vehicle_coverage_id', 'pvc.precut_kit_sale_id', 'pvc.roll_number', 'pvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        /*$wids  = DB::connection('oldWarranty')
            ->table('warranty as w')
            ->where('w.pre_id', '>=', 1)
            ->get(['pre_id']);
        $wids = Arr::pluck($wids, 'pre_id');
        $wids = array_unique($wids);
        $p =  DB::connection('oldWarranty')
            ->table('precut_kit as w')
            ->get('precut_autoincrement_id');
        $pids = Arr::pluck($p, 'precut_autoincrement_id');
        return [count($pids), array_diff($wids, $pids)];*/
        // asort($rst);
        $upload = [];

        $tdate = date('Y-m-d H:i:s');
        foreach ($rst as $k => $v) {
            $ar = explode('#', $k, 2);
            $item = [
                'user_id' => $ar[0],
                'roll_number' => $ar[1],
                'restlen' => $v,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";

        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);

        return [count($rst)];

        $count = DB::connection('oldWarranty')
            ->table('precut_kit')
            ->count();

        return [count($data), $count, $count2];

    }

    /*安装部位*/
    public function up3()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $sql = '';
        /*
         * 质保安装部位*/
        $count = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->count();
        $m = ceil($count/10000);
        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO warranty_to_vehicle_coverage (`id`, `warranty_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        /*
         * 专车专用套件*/
        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut_to_vehicle_coverage (`id`, `precut_kit_sale_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        return $sql;

        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);
        return [$data[0]];

    }

    /*卷号以及分配记录*/
    public function up2()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->count();
        $m = ceil($count/10000);
        $sql = '';
        // $rno = [];  // ID对应卷号
        $roll_length = []; // ID长度
        // $rno_length = [];   // 卷号长度
        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_master')
                ->select(['roll_id as id', 'roll_id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'date_entered'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'created_at', 'updated_at'];
            foreach ($data as $k=>$v){
                // $rno[$v->id] = $v->roll_number;
                $roll_length[$v->id] = $v->length;
                // $rno_length[$v->roll_number] = $v->length;
                if (strtotime($v->date_entered) < strtotime($bdate)) {
                    $v->date_entered = $bdate;
                } else {
                    $v->date_entered = date('Y-m-d H:i:s', strtotime($v->date_entered));
                }
                $v->created_at = $v->date_entered;
                $v->updated_at = $v->date_entered;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_master (`id`, `roll_number`, `film_type_id`, `width`, `length`, `product_type_id`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }

        $count = $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->count();
        $m = ceil($count/10000);

        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_sales')
                ->select(['roll_sales_id as id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_sales_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut', 'length', 'type', 'created_at', 'updated_at', 'created_by'];
            foreach ($data as $k=>$v){
                if (strtotime($v->date_sold) < strtotime($bdate)) {
                    $v->date_sold = $bdate;
                } else {
                    $v->date_sold = date('Y-m-d H:i:s', strtotime($v->date_sold));
                }
                $v->created_at = $v->date_sold;
                $v->updated_at = $v->date_sold;
                $v->length = @$roll_length[$v->roll_id];
                $v->type = 1;
                $v->created_by = $v->sold_by_user_id;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_sales (`id`, `roll_id`, `sold_by_user_id`, `sold_to_user_id`, `date_sold`, `is_precut`, `length`, `type`, `created_at`, `updated_at`, `created_by`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        return $sql;
        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);
/*
        $rnos = [$rno, $roll_length, $rno_length];
        $filename = __DIR__.'/rnos_20201113.json';
        file_put_contents($filename, json_encode($rnos));*/

        return [$upload[1]];
    }

    private function sync_database()
    {
        $c = DB::table('users')->count();
        $no = 1000;
        $m = ceil($c/$no);
        $data = $bool = [];
        for ($i=0;$i<$m;$i++) {
            $data = DB::table('users')->offset($i*$no)->limit($no)->get();
            $upload = [];
            foreach ($data as $k=>$v) {
                $upload[] = (array) $v;
            }
            // $data = DB::connection('newWarranty')->table('users')->get();
            $bool = DB::connection('newWarranty')->table('users')->insert($upload);
        }
        return [$bool];
    }

    public function up1()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('warranty')
            ->count();
        $m = ceil($count/10000);
        $sql = '';
        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('warranty')
                ->select(['warranty_id as id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'user_id as fq_user', 'installation_date as fq_date', DB::raw('1 as approved'), DB::raw('1 as approved_by_user_id'), 'installation_date as approved_date', DB::raw('"'.$tdate.'" as created_at'), DB::raw('"'.$tdate.'" as updated_at')])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('warranty_id')
                ->get();
            $upload = [];
            $array = ['id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'fq_user', 'fq_date', 'approved', 'approved_by_user_id', 'approved_date', 'created_at', 'updated_at'];
            $imagse = [];
            foreach ($data as $k=>$v){
                if ($v->id == 0) {
                    $v->id = 1;
                }
                if (strtotime($v->installation_date) < strtotime($bdate)) {
                    $v->installation_date = $bdate;
                } else {
                    $v->installation_date = date('Y-m-d H:i:s', strtotime($v->installation_date));
                }
                $v->fq_date = $v->installation_date;
                $v->approved_date = $v->installation_date;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
                foreach ([ 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side'] as $ik =>$iv) {
                    if (!empty($v->$iv)) {
                        $imagse_item = [
                            'warranty_id' => $v->id,
                            'part_id' => $ik + 1,
                            'image_src' => $v->$iv,
                            'created_at' => $tdate,
                            'updated_at' => $tdate,
                        ];
                        $imagse[] = array_values($imagse_item);
                    }
                }
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty (`id`, `warranty_id`, `user_id`, `first_name`, `last_name`, `address`, `city`, `province_id`, `zip`, `region_id`, `email_address`, `phone_number`, `installer_name`, `installation_date`, `installation_price`, `image_front`, `image_back`, `image_driver_side`, `image_passenger_side`, `year_id`, `make_id`, `model_id`, `sub_model_id`, `license_plate`, `vin_number`, `additional_comments`, `vehicle_coverage_id`, `pre_id`, `language_id`, `extension`, `fq_user`, `fq_date`, `approved`, `approved_by_user_id`, `approved_date`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
            $data = array_values($imagse);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty_image (`warranty_id`, `part_id`, `image_src`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        $filename = __DIR__.'/test20201113.sql';
        file_put_contents($filename, $sql);
        return [$upload[1]];
        /*// 插入图片
        file_put_contents($filename, $sql, FILE_APPEND);
        return [$upload[1]];*/
    }

    /**/
    public function rest1119()
    {
        $um1 = [];
        $data = DB::connection('newWarranty')
            ->table('roll_master')
            ->select(['id', 'roll_number', 'length'])
            ->orderBy('id')
            ->get();
        $rm = [];
        $rno = [];
        foreach ($data as $k => $v) {
            $key = $v->roll_number;
            $rm[$key] = $v->id;
            $rno[$v->id] = $key;
            $um1['1###'.$key] = $v->length;
        }

        $pvc = DB::connection('newWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut as p', 'pvc.precut_kit_sale_id', 'p.id')
            ->select(['roll_number', 'length', 'p.id as pid', 'created_by'])
            ->get();
        $rus = []; // 主数组
        foreach ($pvc as $k => $v) {
            if (empty($v->pid)) {
                continue;
            }
            if (empty($v->created_by)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->created_by .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }

        $wu = [];
        $wvc = DB::connection('newWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'wvc.warranty_id', 'w.id')
            ->leftJoin('precut as p', 'w.pre_id', 'p.id')
            ->select(['wvc.roll_number', 'wvc.length', 'p.id as pid', 'w.id as wid', 'w.user_id'])
            ->get();
        foreach ($wvc as $k => $v) {
            if (empty($v->wid)) {
                continue;
            }
            if (!empty($v->pid)) {
                continue;
            }
            if (empty($v->user_id)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->user_id .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }
        // return [array_slice($rus, 0, 1)];

        foreach ($rus as $k => $v) {
            if (empty($um1[$k])) {
                $um1[$k] = 0;
            }
            $um1[$k] = $um1[$k] - $v;
        }

        // return [count($um1)];

        /*销售表*/
        $rsb = DB::connection('newWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();

        /*
         * 最后一个拥有全部，含负数*/
        foreach ($rsb as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $key1 = $v->sold_to_user_id.'###'.$roll_number;
            $key2 = $v->sold_by_user_id.'###'.$roll_number;

            $um1[$key1] = @$um1[$key1] + @$um1[$key2]; // 分配对象
            $um1[$key2] = 0; // 被分配对象，清零
        }

        $um2 = $um1;
        $dts = [];
        $urdts = [];

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            if (empty($urdts[$ka[1]])) {
                $urdts[$ka[1]] = [];
            }
            $urdts[$ka[1]][] = $ka[0];
        }

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            $dts = [
                'uid' => $ka[0],
                'rn' => $ka[1],
                'len' => $v,
            ];
            if ($v >= 0) {
                continue;
            }
            if ($ka[0] == 1) {
                continue;
            }
            $aka = '1###'.$ka[1];
            if (empty($um1[$aka])) {
                continue;
            }
            $len = $um1[$aka] + $v;
            if ($len >= 0) {
                $um1[$k] = 0;
                $um1[$aka] = $len;
            } else {
                $um1[$k] = $len;
                $um1[$aka] = 0;
                /*foreach ($urdts[$ka[1]] as $rk => $rv) {
                    $rkr = $rv.'###'.$ka[1];
                    if ($um1[$rkr] > 0 && $um1[$k] < 0) {
                        $len2 = $um1[$rkr] + $um1[$k];
                        if ($len2 >= 0) {
                            $um1[$k] = 0;
                            $um1[$rkr] = $len2;
                        } else {
                            $um1[$k] = $len2;
                            $um1[$rkr] = 0;
                        }
                    }
                }*/
            }
        }
        $diff = [];
        foreach ($um1 as $k => $v) {
            if ($um2[$k] != $v) {
                $diff[$k] = [
                    'old' => $um2[$k],
                    'new' => $v
                ];
            }
        }
        $upload = [];
        $tdate = date('Y-m-d H:i:s');
        foreach ($um1 as $k => $v) {
            $ar = explode('###', $k, 2);
            $item = [
                'user_id' => $ar[0],
                'roll_number' => $ar[1],
                'restlen' => $v,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";
        return $sql;

        $filename = __DIR__.'/test20201119002.sql';
        file_put_contents($filename, $sql);
        return [$filename];
        return [count($diff)];



        /*$filename = __DIR__.'/test20201119.json';
        file_put_contents($filename, json_encode($um1));
        return [$filename];*/
    }

    public function udp111902()
    {
        $filename = __DIR__.'/test20201119.json';
        $um1 = file_get_contents($filename);
        $um1 = json_decode($um1, 1);

        $um2 = $um1;
        $dts = [];
        $urdts = [];

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            if (empty($urdts[$ka[1]])) {
                $urdts[$ka[1]] = [];
            }
            $urdts[$ka[1]][] = $ka[0];
        }

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            $dts = [
                'uid' => $ka[0],
                'rn' => $ka[1],
                'len' => $v,
            ];
            if ($v >= 0) {
                continue;
            }
            if ($ka[0] == 1) {
                continue;
            }
            $aka = '1###'.$ka[1];
            if (empty($um1[$aka])) {
                continue;
            }
            $len = $um1[$aka] + $v;
            if ($len >= 0) {
                $um1[$k] = 0;
                $um1[$aka] = $len;
            } else {
                $um1[$k] = $len;
                $um1[$aka] = 0;
                /*foreach ($urdts[$ka[1]] as $rk => $rv) {
                    $rkr = $rv.'###'.$ka[1];
                    if ($um1[$rkr] > 0 && $um1[$k] < 0) {
                        $len2 = $um1[$rkr] + $um1[$k];
                        if ($len2 >= 0) {
                            $um1[$k] = 0;
                            $um1[$rkr] = $len2;
                        } else {
                            $um1[$k] = $len2;
                            $um1[$rkr] = 0;
                        }
                    }
                }*/
            }
        }
        $diff = [];
        foreach ($um1 as $k => $v) {
            if ($um2[$k] != $v) {
                $diff[$k] = [
                    'old' => $um2[$k],
                    'new' => $v
                ];
            }
        }
        $upload = [];
        $tdate = date('Y-m-d H:i:s');
        foreach ($um1 as $k => $v) {
            $ar = explode('###', $k, 2);
            $item = [
                'user_id' => $ar[0],
                'roll_number' => $ar[1],
                'restlen' => $v,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";

        $filename = __DIR__.'/test20201119002.sql';
        file_put_contents($filename, $sql);
        return [$filename];
        return $sql;

        return [count($diff), $diff];
        $sor = $um1;
        arsort($sor);
        return [array_slice($sor, 0, 10)];
    }

    /**/
    public function rest1123()
    {
        $um1 = [];
        $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->select([DB::raw('roll_id as id'), 'roll_id', 'roll_number', 'length'])
            ->orderBy('roll_id')
            ->get();
        $rm = [];
        $rno = [];
        foreach ($data as $k => $v) {
            $key = $v->roll_number;
            $rm[$key] = $v->id;
            $rno[$v->id] = $key;
            $um1['1###'.$key] = $v->length;
        }

        $pvc = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut_kit as p', 'pvc.precut_kit_sale_id', 'p.precut_autoincrement_id')
            ->select(['roll_number', 'length', 'p.precut_autoincrement_id as pid', 'user_id',  DB::raw('user_id as created_by')])
            ->get();
        $rus = []; // 主数组
        foreach ($pvc as $k => $v) {
            if (empty($v->pid)) {
                continue;
            }
            if (empty($v->created_by)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->created_by .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }

        $wu = [];
        $wvc = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'wvc.warranty_id', 'w.warranty_id')
            ->leftJoin('precut_kit as p', 'w.pre_id', 'p.precut_autoincrement_id')
            ->select(['wvc.roll_number', 'wvc.length', 'p.precut_autoincrement_id as pid', 'w.warranty_id as wid', 'w.user_id'])
            ->get();
        foreach ($wvc as $k => $v) {
            if (empty($v->wid)) {
                continue;
            }
            if (!empty($v->pid)) {
                continue;
            }
            if (empty($v->user_id)) {
                $key = '1###'.$v->roll_number;
            } else {
                $key = $v->user_id .'###'.$v->roll_number;
            }
            $rus[$key] = @$rus[$key] + $v->length;
        }
        // return [array_slice($rus, 0, 1)];

        foreach ($rus as $k => $v) {
            if (empty($um1[$k])) {
                $um1[$k] = 0;
            }
            $um1[$k] = $um1[$k] - $v;
        }

        // return [count($um1)];

        /*销售表*/
        $rsb = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();

        /*
         * 最后一个拥有全部，含负数*/
        foreach ($rsb as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $key1 = $v->sold_to_user_id.'###'.$roll_number;
            $key2 = $v->sold_by_user_id.'###'.$roll_number;

            $um1[$key1] = @$um1[$key1] + @$um1[$key2]; // 分配对象
            $um1[$key2] = 0; // 被分配对象，清零
        }

        $um2 = $um1;
        $dts = [];
        $urdts = [];

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            if (empty($urdts[$ka[1]])) {
                $urdts[$ka[1]] = [];
            }
            $urdts[$ka[1]][] = $ka[0];
        }

        foreach ($um1 as $k => $v) {
            $ka = explode('###', $k);
            $dts = [
                'uid' => $ka[0],
                'rn' => $ka[1],
                'len' => $v,
            ];
            if ($v >= 0) {
                continue;
            }
            if ($ka[0] == 1) {
                continue;
            }
            $aka = '1###'.$ka[1];
            if (empty($um1[$aka])) {
                continue;
            }
            $len = $um1[$aka] + $v;
            if ($len >= 0) {
                $um1[$k] = 0;
                $um1[$aka] = $len;
            } else {
                $um1[$k] = $len;
                $um1[$aka] = 0;
                /*foreach ($urdts[$ka[1]] as $rk => $rv) {
                    $rkr = $rv.'###'.$ka[1];
                    if ($um1[$rkr] > 0 && $um1[$k] < 0) {
                        $len2 = $um1[$rkr] + $um1[$k];
                        if ($len2 >= 0) {
                            $um1[$k] = 0;
                            $um1[$rkr] = $len2;
                        } else {
                            $um1[$k] = $len2;
                            $um1[$rkr] = 0;
                        }
                    }
                }*/
            }
        }
        $diff = [];
        foreach ($um1 as $k => $v) {
            if ($um2[$k] != $v) {
                $diff[$k] = [
                    'old' => $um2[$k],
                    'new' => $v
                ];
            }
        }
        $upload = [];
        $tdate = date('Y-m-d H:i:s');
        foreach ($um1 as $k => $v) {
            $ar = explode('###', $k, 2);
            $item = [
                'user_id' => @$ar[0] ?: 0,
                'roll_number' => $ar[1],
                'restlen' => $v ?: 0,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";
        return $sql;
    }

    public function update1119()
    {
        $sql1 = $this->wtRolls(); // 安装部位
        $sql2 = $this->wtCoverage(); //  卷号以及分配记录
        $sql3 = $this->wtPrecut();
        $sql4 = $this->wtWarranty();
        $sql5 = $this->rest1123();
        $sql = $sql1 . $sql2 .  $sql3 . $sql4 . $sql5;
        $filename = __DIR__.'/test20201123.sql';
        file_put_contents($filename, $sql);
        return [$filename];
    }

    protected function wtRestlen()
    {
        $rno = []; // ID 卷号对应数组
        $rnl = [];  // 卷号对应长度

        $rst = []; // 用户#卷号=长度
        // 主表
        $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->select(['roll_id', 'roll_number', 'length'])
            ->orderBy('roll_id')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = trim($v->roll_number);
            $key = '1#'.$roll_number;
            $rst[$key] = $v->length;
            $rno[$v->roll_id] = $roll_number;
            $rnl[$roll_number] = $v->length;
        }
        // 销售表，最后一个人拥有全部
        $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->select(['roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('date_sold')
            ->get();
        foreach ($data as $k => $v) {
            $roll_number = @$rno[$v->roll_id];
            if (empty($roll_number)) {
                continue;
            }
            $key = $v->sold_to_user_id.'#'.$roll_number;
            $rst[$key] = 0;
            $key = $v->sold_by_user_id.'#'.$roll_number;
            $rst[$key] = $rnl[$roll_number];
        }
        // 质保表，获取全部，套件表仅获取未安装
        $pdu = []; // 套件ID对应用户ID
        $p =  DB::connection('oldWarranty')
            ->table('precut_kit as w')
            ->get(['precut_autoincrement_id', 'user_id']);
        foreach ($p as $k=>$v) {
            $pdu[$v->precut_autoincrement_id] = $v->user_id;
        }

        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage as wvc')
            ->leftJoin('warranty as w', 'w.warranty_id', 'wvc.warranty_id')
            ->select(['w.pre_id', 'w.user_id', 'w.pre_id', 'wvc.vehicle_coverage_id', 'wvc.warranty_id', 'wvc.roll_number', 'wvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (!empty($v->pre_id)) {
                $user_id = @$pdu[$v->pre_id] ?: $v->user_id;
            }
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage as pvc')
            ->leftJoin('precut_kit as p', 'p.precut_autoincrement_id', 'pvc.precut_kit_sale_id')
            ->where('p.installed', '!=', 'Yes')
            ->select(['p.precut_autoincrement_id', 'p.user_id', 'pvc.vehicle_coverage_id', 'pvc.precut_kit_sale_id', 'pvc.roll_number', 'pvc.length'])
            ->get();
        foreach ($data as $k => $v) {
            $user_id = $v->user_id;
            if (empty($user_id) || empty($v->roll_number)) {
                continue;
            }
            $key = $user_id . '#' . $v->roll_number;
            if (empty($rst[$key])) {
                $rst[$key] = 0;
            }
            $rst[$key] = $rst[$key] - $v->length;
        }

        $upload = [];
        $tdate = date('Y-m-d H:i:s');
        foreach ($rst as $k => $v) {
            $ar = explode('#', $k, 2);
            $item = [
                'user_id' => $ar[0],
                'roll_number' => $ar[1],
                'restlen' => $v,
                'created_at' => $tdate,
                'updated_at' => $tdate,
            ];
            $upload[] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO roll_restlen (`user_id`, `roll_number`, `restlen`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";

        return $sql;
    }

    protected function wtRolls()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('roll_master')
            ->count();
        $m = ceil($count/10000);
        $sql = '';
        // $rno = [];  // ID对应卷号
        $roll_length = []; // ID长度
        // $rno_length = [];   // 卷号长度
        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_master')
                ->select(['roll_id as id', 'roll_id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'date_entered'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_number', 'film_type_id', 'width', 'length', 'product_type_id', 'created_at', 'updated_at'];
            foreach ($data as $k=>$v){
                // $rno[$v->id] = $v->roll_number;
                $roll_length[$v->id] = $v->length;
                // $rno_length[$v->roll_number] = $v->length;
                if (strtotime($v->date_entered) < strtotime($bdate)) {
                    $v->date_entered = $bdate;
                } else {
                    $v->date_entered = date('Y-m-d H:i:s', strtotime($v->date_entered));
                }
                $v->length = (int) $v->length;
                $v->created_at = $v->date_entered;
                $v->updated_at = $v->date_entered;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_master (`id`, `roll_number`, `film_type_id`, `width`, `length`, `product_type_id`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }

        $count = $data = DB::connection('oldWarranty')
            ->table('roll_sales')
            ->count();
        $m = ceil($count/10000);

        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('roll_sales')
                ->select(['roll_sales_id as id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut'])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('roll_sales_id')
                ->get();
            $upload = [];
            $array = ['id', 'roll_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold', 'is_precut', 'length', 'type', 'created_at', 'updated_at', 'created_by'];
            foreach ($data as $k=>$v){
                if (strtotime($v->date_sold) < strtotime($bdate)) {
                    $v->date_sold = $bdate;
                } else {
                    $v->date_sold = date('Y-m-d H:i:s', strtotime($v->date_sold));
                }
                $v->created_at = $v->date_sold;
                $v->updated_at = $v->date_sold;
                $v->length = @$roll_length[$v->roll_id] ?: 0;
                $v->type = 1;
                $v->created_by = $v->sold_by_user_id;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO roll_sales (`id`, `roll_id`, `sold_by_user_id`, `sold_to_user_id`, `date_sold`, `is_precut`, `length`, `type`, `created_at`, `updated_at`, `created_by`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }

        return $sql;
    }

    protected function wtCoverage()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $sql = '';
        /*
         * 质保安装部位*/
        $count = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->count();
        $m = ceil($count/10000);
        $data = DB::connection('oldWarranty')
            ->table('warranty_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'warranty_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO warranty_to_vehicle_coverage (`id`, `warranty_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        /*
         * 专车专用套件*/
        $data = DB::connection('oldWarranty')
            ->table('precut_to_vehicle_coverage')
            ->select(['vehicle_coverage_id as id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width','length'])
            ->get();
        $array = ['id', 'precut_kit_sale_id', 'part_id', 'precut_or_manual', 'roll_number', 'film_type_id', 'width', 'length', 'created_at', 'updated_at'];
        $upload = [];
        foreach ($data as $k=>$v){
            $v->created_at = $bdate;
            $v->updated_at = $bdate;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut_to_vehicle_coverage (`id`, `precut_kit_sale_id`, `part_id`, `precut_or_manual`, `roll_number`, `film_type_id`, `width`, `length`, `created_at`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";
        return $sql;
    }

    protected function wtPrecut()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $sql = '';
        $count = $data = DB::connection('oldWarranty')
            ->table('precut_kit')
            ->count();
        $m = ceil($count/10000);

        // 套件分配
        $data = DB::connection('oldWarranty')
            ->table('precut_kit_sales')
            ->select(['precut_kit_sale_id as id', 'precut_autoincrement_id as precut_id', 'sold_by_user_id', 'sold_to_user_id', 'date_sold'])
            ->orderBy('precut_kit_sale_id')
            ->get();
        $upload = [];
        $array = ['id', 'precut_id', 'sold_by_user_id', 'sold_to_user_id', 'type', 'created_at', 'created_by', 'updated_at'];
        $sold = [];
        foreach ($data as $k=>$v){
            $sold[$v->precut_id] = $v->sold_to_user_id;
            if (strtotime($v->date_sold) < strtotime($bdate)) {
                $v->date_sold = $bdate;
            } else {
                $v->date_sold = date('Y-m-d H:i:s', strtotime($v->date_sold));
            }
            $v->created_at = $v->date_sold;
            $v->updated_at = $v->date_sold;
            $v->type = 1;
            $v->created_by = $v->sold_by_user_id;
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                if (mb_stripos($text, '&#')===0) {
                    $text = str_replace('&#', ';&#', $text).';';
                    $text = mb_substr($text, 1);
                }
                $text = html_entity_decode($text);
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut_assign (`id`, `precut_id`, `sold_by_user_id`, `sold_to_user_id`, `type`, `created_at`, `created_by`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";
        // 套件
        $wpk = DB::connection('oldWarranty')
            ->table('warranty')
            ->select('pre_id', 'warranty_id')
            ->get();
        $wpks = [];
        foreach ($wpk as $k => $v) {
            $wpks[$v->pre_id] = 1;
        }
        $data = DB::connection('oldWarranty')
            ->table('precut_kit as pk')
            // ->leftJoin('warranty as w', 'w.pre_id', 'pk.precut_autoincrement_id')
            //  as id
            ->select(['pk.precut_autoincrement_id as id', 'pk.user_id', 'pk.precut_id', 'pk.year_id', 'pk.make_id', 'pk.model_id', 'pk.installed', 'pk.created_date', 'pk.created_date as created_at', 'pk.user_id as created_by', 'pk.created_date as updated_at'])
            // ->groupBy('pk.precut_autoincrement_id')
            ->get();
        $upload = [];
        $array = ['id', 'user_id', 'precut_id', 'year_id', 'make_id', 'model_id', 'status', 'created_at', 'created_by', 'updated_at'];
        foreach ($data as $k => $v) {
            $item = [];
            if (strtotime($v->created_date) < strtotime($bdate)) {
                $v->created_date = $bdate;
            } else {
                $v->created_date = date('Y-m-d H:i:s', strtotime($v->created_date));
            }
            // $v->id = $v->precut_autoincrement_id;
            $v->created_at = $v->created_date;
            $v->updated_at = $v->created_date;
            $v->type = 1;
            $v->created_by = $v->user_id;
            $v->user_id = @$sold[$v->id] ?: 0;
            // $v->status = $v->installed == 'Yes' ? 1 : 0;
            if (@$wpks[$v->id]) {
                $v->status = 1;
            } else {
                $v->status = 0;
            }
            /*if ($v->warranty_id) {
                $v->status = 1;
            } else {
                $v->status = 0;
            }*/
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                if (mb_stripos($text, '&#')===0) {
                    $text = str_replace('&#', ';&#', $text).';';
                    $text = mb_substr($text, 1);
                }
                $text = html_entity_decode($text);
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $upload[$k] = array_values($item);
        }
        $data = array_values($upload);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO precut (`id`, `user_id`, `precut_id`, `year_id`, `make_id`, `model_id`, `status`, `created_at`, `created_by`, `updated_at`) VALUES \n";
        $sql .= $ins.$string."; \n";

        return $sql;
    }

    protected function wtWarranty()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 02:01:01';
        $count = $data = DB::connection('oldWarranty')
            ->table('warranty')
            ->count();
        $m = ceil($count/10000);
        $sql = '';
        $user = DB::connection('oldWarranty')
            ->table('warranty')
            ->select(['id', 'company_name'])
            ->get();
        foreach ($user as $k => $v) {

        }

        for ($i=0;$i<$m;$i++) {
            $data = DB::connection('oldWarranty')
                ->table('warranty')
                ->select(['warranty_id as id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'user_id as fq_user', 'installation_date as fq_date', DB::raw('1 as approved'), DB::raw('1 as approved_by_user_id'), 'installation_date as approved_date', DB::raw('"'.$tdate.'" as created_at'), DB::raw('"'.$tdate.'" as updated_at')])
                ->limit(10000)
                ->offset($i*10000)
                ->orderBy('warranty_id')
                ->get();
            $upload = [];
            $array = ['id', 'warranty_id', 'user_id', 'first_name', 'last_name', 'address', 'city', 'province_id', 'zip', 'region_id', 'email_address', 'phone_number', 'installer_name', 'installation_date', 'installation_price', 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side', 'year_id', 'make_id', 'model_id', 'sub_model_id', 'license_plate', 'vin_number', 'additional_comments', 'vehicle_coverage_id', 'pre_id', 'language_id', 'extension', 'fq_user', 'fq_date', 'approved', 'approved_by_user_id', 'approved_date', 'created_at', 'updated_at'];
            $imagse = [];
            foreach ($data as $k=>$v){
                if ($v->id == 0) {
                    $v->id = 1;
                }
                if (strtotime($v->installation_date) < strtotime($bdate)) {
                    $v->installation_date = $bdate;
                } else {
                    $v->installation_date = date('Y-m-d H:i:s', strtotime($v->installation_date));
                }
                $v->fq_date = $v->installation_date;
                $v->approved_date = $v->installation_date;
                $item = [];
                foreach ($array as $ak => $av) {
                    $text = $v->$av;
                    if (mb_stripos($text, '&#')===0) {
                        $text = str_replace('&#', ';&#', $text).';';
                        $text = mb_substr($text, 1);
                    }
                    $text = html_entity_decode($text);
                    $item[$ak] = $text;
                }
                $item = array_values($item);
                $upload[$k] = array_values($item);
                foreach ([ 'image_front', 'image_back', 'image_driver_side', 'image_passenger_side'] as $ik =>$iv) {
                    if (!empty($v->$iv)) {
                        $imagse_item = [
                            'warranty_id' => $v->id,
                            'part_id' => $ik + 1,
                            'image_src' => $v->$iv,
                            'created_at' => $tdate,
                            'updated_at' => $tdate,
                        ];
                        $imagse[] = array_values($imagse_item);
                    }
                }
            }
            $data = array_values($upload);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty (`id`, `warranty_id`, `user_id`, `first_name`, `last_name`, `address`, `city`, `province_id`, `zip`, `region_id`, `email_address`, `phone_number`, `installer_name`, `installation_date`, `installation_price`, `image_front`, `image_back`, `image_driver_side`, `image_passenger_side`, `year_id`, `make_id`, `model_id`, `sub_model_id`, `license_plate`, `vin_number`, `additional_comments`, `vehicle_coverage_id`, `pre_id`, `language_id`, `extension`, `fq_user`, `fq_date`, `approved`, `approved_by_user_id`, `approved_date`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
            $data = array_values($imagse);
            $string = json_encode($data, JSON_UNESCAPED_UNICODE);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO warranty_image (`warranty_id`, `part_id`, `image_src`, `created_at`, `updated_at`) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        return $sql;
    }

    protected function wtUser()
    {
        $tdate = date('Y-m-d H:i:s');
        $bdate = '1970-01-02 01:01:01';

        $data = DB::connection('oldWarranty')
            ->table('users')
            ->select(['user_id as id', 'creator_id', 'user_type', 'unique_id', 'company_name', DB::raw('company_name as abbr'), 'first_name', 'last_name', 'username', 'email_address', 'password', 'address', 'city', 'province_id', 'zip', 'region_id', 'phone_number', 'note', 'cell_phone', 'wechat_id', 'date_signedup', 'additional_comment', 'province', 'status', DB::raw(' 0 as mdtype'), 'language_id', 'extension', DB::raw('"'.$tdate.'" as created_at'), DB::raw('"'.$tdate.'" as updated_at')])
            ->get();
        $users = [];
        $array = ['id', 'creator_id', 'user_type', 'unique_id', 'company_name', 'abbr', 'first_name', 'last_name', 'username', 'email_address', 'password', 'address', 'city', 'province_id', 'zip', 'region_id', 'phone_number', 'note', 'cell_phone', 'wechat_id', 'date_signedup', 'additional_comment', 'province', 'status', 'mdtype', 'language_id', 'extension', 'created_at', 'updated_at'];
        foreach ($data as $k=>$v){
            if ($v->date_signedup < $bdate) {
                $v->date_signedup = $bdate;
            }
            $item = [];
            foreach ($array as $ak => $av) {
                $text = $v->$av;
                if (mb_stripos($text, '&#')===0) {
                    $text = str_replace('&#', ';&#', $text).';';
                    $text = mb_substr($text, 1);
                }
                $text = html_entity_decode($text);
                $item[$ak] = $text;
            }
            $item = array_values($item);
            $users[$k] = array_values($item);
        }
        $data = array_values($users);
        $string = json_encode($data, JSON_UNESCAPED_UNICODE);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $ins = "INSERT INTO users (`id`, `creator_id`, `user_type`, `unique_id`, `company_name`, `abbr`, `first_name`, `last_name`, `username`, `email_address`, `password`, `address`, `city`, `province_id`, `zip`, `region_id`, `phone_number`, `note`, `cell_phone`, `wechat_id`, `date_signedup`, `additional_comment`, `province`, `status`, `mdtype`, `language_id`, `extension`, `created_at`, `updated_at`) VALUES \n";
        $sql = $ins.$string."; \n";
        return $sql;
    }

    public function aa1()
    {
        return [range('A', 'Z'), range('AA', 'BZ')];
    }

    public function bew1111($no=16)
    {
        $int = range(0,9);
        $lower = range('a', 'z');
        $upper = range('A', 'Z');
        $fs = ['!', '@', '#', '$', '%', '^', '&', '*'];
        $all = array_merge($int, $lower, $upper, $fs);
        // a8iKQ
        $no = rand(1, 16);
        $pw = [];
        $pwa = [];
        $no = 5;
        $len = count($all);
        if ($no < 4) {
            $pwa = array_rand($all, 4);
        } else {
            $pwa[] = rand(0,9);
            $pwa[] = rand(0,25) + 10;
            $pwa[] = rand(0,25) + 10 + 26;
            $pwa[] = rand(62,$len-1);
            for ($i=5;$i<=$no;$i++) {
                $pwa[] = rand(0, $len-1);
            }
        }
        foreach ($pwa as $v) {
            $pw[] = $all[$v];
        }
        shuffle($pw);
        return [implode('', $pw), $len, $pwa];

        $d = DB::connection('report')->table('account')->get();
        return [base64_encode('8pMkfE!mzFWf5VSy'), encrypt('8pMkfE!mzFWf5VSy')];
        // base64_decode()

        $e = encrypt('XjaCEJCGJfxdJbjs');

        $pl = base64_decode($e);
        $payload = json_decode($pl, 1);
        // Crypt::decrypt()
        $iv = base64_decode($payload['iv']);
        $key = base64_encode('hi+my4I8AjBVvdpf601/9QSkNFC6PolqgAO+kmJydOc=');
        $key = 'hi+my4I8AjBVvdpf601/9QSkNFC6PolqgAO+kmJydOc=';
        $key = base64_decode($key);
        $decrypted = openssl_decrypt(
            $payload['value'], 'AES-128-CBC', $key, 0, $iv
        );

        return [$e, $payload, $decrypted];
        $v ='';

        /*$decrypted = \openssl_decrypt(
            $payload['value'], $this->cipher, $this->key, 0, $iv
        );*/
        openssl_decrypt();
        // Crypt::decrypt()
    }

    public function bew1110()
    {
        $data = [];
        $uid = ['CN00001','CN00002','CN00003','CN00006','CN00009','CN00033','CN00039','CN00040','CN02221','CN00017','CN00028','CN00037','CN00016','CN00005','CN00014','CN00004','CN00019','CN00023','CN00026','CN00627','CN00029','CN00031','CN00032','CN00034','CN00036','CN00038','CN00042','CN00043','CN00044','CN00046','CN00047','CN00048','CN02144','CN00160','CN00160','CN00234','CN00234','CN00867','CN00867','CN01104','CN01104','CN01137','CN01137','CN01281','CN01281','CN01620','CN01620','CN01668','CN01668','CN01691','CN01691','CN01806','CN01806','CN01875','CN01875','CN01889','CN01889','CN02123','CN02123','CN02429','CN02429','CN02454','CN02454','123456','CN00045','CN01294','CN00674','CN02008','ASP025','CN00016','CN00018','CN00023','CN00026','CN00029','CN00032','CN00038','CN00045','CN00065','CN00075','CN00082','CN00083','CN00090','CN00093','CN00094','CN00105','CN00108','CN0011','CN00158','CN00159','CN00169','CN00177','CN00182','CN00184','CN00186','CN00189','CN00201','CN00218','CN00226','CN00243','CN00244','CN00245','CN00255','CN00256','CN00263','CN00276','CN00284','CN00311','CN00322','CN00325','CN00326','CN00327','CN00347','CN00356','CN00359','CN00361','CN00362','CN00363','CN00368','CN00369','CN00377','CN00378','CN00380','CN00386','CN00391','CN00395','CN00402','CN00403','CN00408','CN00409','CN00414','CN00415','CN00417','CN00426','CN00427','CN00428','CN00447','CN00450','CN00456','CN00461','CN00465','CN00469','CN00474','CN00476','CN00484','CN00485','CN00488','CN00491','CN00492','CN00493','CN00498','CN00500','CN00502','CN00503','CN00507','CN00512','CN00514','CN00524','CN00534','CN00538','CN00541','CN00546','CN00548','CN00550','CN00552','CN00554','CN00558','CN00560','CN00561','CN00566','CN00569','CN00580','CN00584','CN00586','CN00594','CN00595','CN00601','CN00602','CN00605','CN00606','CN00617','CN00621','CN00625','CN00626','CN00628','CN00630','CN00632','CN00637','CN00638','CN00643','CN00644','CN00647','CN00650','CN00655','CN00656','CN00659','CN006591','CN00660','CN00661','CN00662','CN00664','CN00668','CN00669','CN00673','CN00676','CN00677','CN00680','CN00683','CN00690','CN00691','CN00692','CN00693','CN00694','CN00695','CN00696','CN00697','CN00700','CN00701','CN00702','CN00704','CN00707','CN00714','CN00715','CN00721','CN00724','CN00725','CN00727','CN00728','CN00729','CN00731','CN00733','CN00734','CN00735','CN00736','CN00738','CN00740','CN00741','CN00745','CN00746','CN00749','CN00750','CN00751','CN00761','CN00766','CN00770','CN00783','CN00787','CN00788','CN00789','CN00792','CN00793','CN00800','CN00803','CN00806','CN00808','CN00809','CN00812','CN00813','CN00817','CN00819','CN00820','CN00823','CN00827','CN00832','CN00842','CN00844','CN00845','CN00847','CN00852','CN00854','CN00855','CN00856','CN00860','CN00862','CN00864','CN00866','CN00868','CN00869','CN00870','CN00873','CN00875','CN00881','CN00882','CN00883','CN00889','CN00892','CN00897','CN00899','CN00900','CN00904','CN00909','CN00910','CN00913','CN00914','CN00915','CN00919','CN00924','CN00925','CN00928','CN00929','CN00935','CN00937','CN00938','CN00939','CN00941','CN00943','CN00946','CN00949','CN00951','CN00952','CN00960','CN00961','CN00962','CN00963','CN00964','CN009656','CN00967','CN00968','CN00976','CN00982','CN00984','CN00986','CN00989','CN00993','CN00997','CN00998','CN01000','CN01002','CN01003','CN01004','CN01008','CN01011','CN01014','CN01016','CN01018','CN01020','CN01021','CN01022','CN01023','CN01027','CN01029','CN01031','CN01034','CN01036','CN01037','CN01038','CN01039','CN01042','CN01044','CN01049','CN01050','CN01051','CN01055','CN01058','CN01059','CN01060','CN01061','CN01062','CN01065','CN01066','CN01067','CN01068','CN01069','CN01077','CN01079','CN01080','CN01085','CN01090','CN01091','CN01093','CN01094','CN01096','CN01097','CN01098','CN01099','CN01103','CN01105','CN01106','CN01107','CN01109','CN01110','CN01111','CN01113','CN01114','CN01119','CN01121','CN01122','CN01123','CN01124','CN01128','CN01130','CN01131','CN01132','CN01133','CN01135','CN01136','CN01138','CN01141','CN01142','CN01143','CN01149','CN01152','CN01153','CN01154','CN01155','CN01156','CN01159','CN01160','CN01161','CN01163','CN01164','CN01165','CN01170','CN01178','CN01179','CN01182','CN01184','CN01185','CN01189','CN01190','CN01191','CN01192','CN01193','CN01194','CN01195','CN01196','CN01198','CN01200','CN01203','CN01204','CN01207','CN01209','CN01211','CN01212','CN01213','CN01215','CN01216','CN01217','CN01219','CN01220','CN01225','CN01226','CN01227','CN01228','CN01229','CN01230','CN01231','CN01235','CN01236','CN01240','CN01242','CN01243','CN01256','CN01257','CN01260','CN01261','CN01263','CN01264','CN01265','CN01266','CN01270','CN01271','CN01272','CN01275','CN01279','CN01280','CN01285','CN01286','CN01287','CN01288','CN01290','CN01291','CN01292','CN01295','CN01297','CN01298','CN01300','CN01301','CN01302','CN01303','CN01304','CN01305','CN01307','CN01308','CN01309','CN01310','CN01311','CN01312','CN01313','CN01316','CN01318','CN01322','CN01327','CN01328','CN01332','CN01334','CN01335','CN01337','CN01338','CN01339','CN01344','CN01345','CN01346','CN01347','CN01348','CN01349','CN01350','CN01351','CN01352','CN01353','CN01355','CN01356','CN01358','CN01366','CN01367','CN01368','CN01370','CN01371','CN01373','CN01375','CN01378','CN01379','CN01381','CN01384','CN01386','CN01388','CN01389','CN01391','CN01392','CN01396','CN01398','CN01399','CN01402','CN01403','CN01404','CN01406','CN01411','CN01412','CN01413','CN01415','CN01416','CN01417','CN01419','CN01421','CN01422','CN01424','CN01425','CN01426','CN01429','CN01430','CN01451','CN01453','CN01454','CN01455','CN01456','CN01458','CN01459','CN01463','CN01464','CN01465','CN01468','CN01469','CN01470','CN01471','CN01475','CN01479','CN01484','CN01485','CN01486','CN01487','CN01488','CN01490','CN01493','CN01494','CN01495','CN01500','CN01501','CN01502','CN01505','CN01508','CN01509','CN01510','CN01511','CN01512','CN01514','CN01515','CN01516','CN01518','CN01521','CN01522','CN01524','CN01526','CN01527','CN01528','CN01529','CN01531','CN01532','CN01534','CN01535','CN01537','CN01539','CN01540','CN01541','CN01543','CN01544','CN01545','CN01546','CN01547','CN01548','CN01551','CN01552','CN01553','CN01554','CN01555','CN01556','CN01557','CN01560','CN01562','CN01563','CN01566','CN01567','CN01568','CN01569','CN01570','CN01571','CN01572','CN01575','CN01577','CN01579','CN01580','CN01581','CN01584','CN01585','CN01588','CN01589','CN01591','CN01595','CN01596','CN01597','CN01601','CN01602','CN01603','CN01604','CN01605','CN01606','CN01611','CN01612','CN01613','CN01615','CN01616','CN01617','CN01618','CN01619','CN01621','CN01622','CN01623','CN01624','CN01625','CN01626','CN01627','CN01629','CN01630','CN01631','CN01632','CN01634','CN01635','CN01636','CN01638','CN01640','CN01641','CN01645','CN01646','CN01649','CN01650','CN01652','CN01653','CN01654','CN01655','CN01656','CN01657','CN01659','CN01662','CN01663','CN01666','CN01667','CN01669','CN01670','CN01671','CN01672','CN01677','CN01680','CN01681','CN01683','CN01684','CN01685','CN01686','CN01687','CN01689','CN01692','CN01693','CN01694','CN01696','CN01700','CN01703','CN01704','CN01706','CN01708','CN01709','CN01710','CN01712','CN01713','CN01714','CN01715','CN01716','CN01717','CN01718','CN01719','CN01720','CN01721','CN01724','CN01725','CN01726','CN01727','CN01728','CN01729','CN01733','CN01734','CN01736','CN01738','CN01739','CN01740','CN01741','CN01743','CN01745','CN01746','CN01747','CN01749','CN01750','CN01752','CN01754','CN01755','CN01756','CN01758','CN01759','CN01760','CN01761','CN01762','CN01763','CN01764','CN01766','CN01767','CN01769','CN01770','CN01772','CN01773','CN01774','CN01775','CN01778','CN01779','CN01780','CN01781','CN01782','CN01786','CN01787','CN01789','CN01790','CN01792','CN01793','CN01794','CN01795','CN01796','CN01807','CN01808','CN01809','CN01810','CN01811','CN01812','CN01813','CN01814','CN01817','CN01818','CN01819','CN01820','CN01821','CN01822','CN01823','CN01824','CN01826','CN01827','CN01828','CN01829','CN01830','CN01831','CN01832','CN01836','CN01839','CN01840','CN01841','CN01842','CN01843','CN01844','CN01845','CN01847','CN01849','CN01850','CN01851','CN01852','CN01854','CN01855','CN01856','CN01857','CN01858','CN01860','CN01861','CN01863','CN01865','CN01869','CN01870','CN01872','CN01874','CN01876','CN01877','CN01878','CN01879','CN01880','CN01881','CN01882','CN01886','CN01887','CN01891','CN01895','CN01897','CN01898','CN01899','CN01900','CN01902','CN01903','CN01904','CN01905','CN01907','CN01908','CN01909','CN01911','CN01912','CN01916','CN01919','CN01922','CN01923','CN01924','CN01925','CN01928','CN01929','CN01930','CN01931','CN01934','CN01938','CN01940','CN01941','CN01942','CN01943','CN01944','CN01945','CN01948','CN01950','CN01951','CN01952','CN01953','CN01955','CN01956','CN01957','CN01958','CN01959','CN01960','CN01961','CN01962','CN01963','CN01964','CN01965','CN01966','CN01967','CN01968','CN01969','CN01971','CN01972','CN01973','CN01974','CN01975','CN01976','CN01977','CN01981','CN01983','CN01984','CN01987','CN01988','CN01989','CN01990','CN01991','CN01992','CN01993','CN01994','CN01995','CN01999','CN02000','CN02001','CN02002','CN02003','CN02004','CN02006','CN02007','CN02009','CN02012','CN02013','CN02014','CN02016','CN02017','CN02019','CN02020','CN02021','CN02023','CN02024','CN02025','CN02026','CN02027','CN02028','CN02029','CN02034','CN02035','CN02036','CN02037','CN02038','CN02039','CN02040','CN02042','CN02043','CN02044','CN02045','CN02046','CN02047','CN02048','CN02049','CN02050','CN02051','CN02052','CN02053','CN02054','CN02055','CN02056','CN02057','CN02058','CN02059','CN02060','CN02061','CN02062','CN02063','CN02064','CN02065','CN02066','CN02067','CN02068','CN02069','CN02070','CN02071','CN02072','CN02073','CN02074','CN02075','CN02076','CN02077','CN02078','CN02079','CN02080','CN02081','CN02082','CN02083','CN02084','CN02085','CN02086','CN02087','CN02088','CN02089','CN02090','CN02091','CN02092','CN02094','CN02095','CN02096','CN02097','CN02098','CN02099','CN02100','CN02101','CN02102','CN02103','CN02104','CN02105','CN02106','CN02107','CN02108','CN02109','CN02110','CN02111','CN02112','CN02113','CN02114','CN02115','CN02116','CN02117','CN02118','CN02119','CN02120','CN02121','CN02122','CN02124','CN02125','CN02126','CN02127','CN02128','CN02129','CN02130','CN02131','CN02132','CN02133','CN02134','CN02135','CN02136','CN02137','CN02138','CN02141','CN02142','CN02143','CN02145','CN02146','CN02147','CN02148','CN02149','CN02150','CN02151','CN02152','CN02153','CN02154','CN02155','CN02156','CN02157','CN02158','CN02159','CN02160','CN02161','CN02162','CN02163','CN02164','CN02165','CN02166','CN02167','CN02168','CN02169','CN02170','CN02172','CN02173','CN02174','CN02175','CN02176','CN02177','CN02178','CN02179','CN02180','CN02182','CN02183','CN02184','CN02185','CN02186','CN02187','CN02188','CN02189','CN02190','CN02191','CN02192','CN02193','CN02194','CN02196','CN02197','CN02199','CN02200','CN02201','CN02202','CN02203','CN02204','CN02205','CN02206','CN02207','CN02208','CN02209','CN02210','CN02211','CN02212','CN02213','CN02214','CN02215','CN02216','CN02217','CN02218','CN02219','CN02220','CN02222','CN02223','CN02224','CN02225','CN02226','CN02227','CN02228','CN02229','CN02230','CN02231','CN02232','CN02233','CN02234','CN02235','CN02237','CN02238','CN02239','CN02240','CN02241','CN02242','CN02243','CN02244','CN02246','CN02247','CN02248','CN02249','CN02250','CN02251','CN02252','CN02253','CN02254','CN02256','CN02257','CN02258','CN02259','CN02260','CN02261','CN02262','CN02263','CN02264','CN02265','CN02266','CN02267','CN02268','CN02269','CN02270','CN02271','CN02272','CN02273','CN02274','CN02276','CN02277','CN02278','CN02279','CN02280','CN02286','CN02288','CN02291','CN02296','CN02297','CN02299','CN02300','CN02301','CN02302','CN02303','CN02304','CN02305','CN02306','CN02307','CN02308','CN02309','CN02310','CN02311','CN02312','CN02313','CN02314','CN02315','CN02316','CN02317','CN02318','CN02319','CN02320','CN02321','CN02322','CN02324','CN02325','CN02326','CN02327','CN02328','CN02329','CN02330','CN02331','CN02332','CN02333','CN02334','CN02335','CN02336','CN02337','CN02338','CN02339','CN02340','CN02341','CN02342','CN02343','CN02344','CN02345','CN02346','CN02347','CN02348','CN02349','CN02350','CN02351','CN02353','CN02354','CN02355','CN02357','CN02358','CN02359','CN02360','CN02361','CN02362','CN02363','CN02364','CN02365','CN02366','CN02367','CN02368','CN02369','CN02370','CN02371','CN02372','CN02373','CN02374','CN02376','CN02377','CN02378','CN02379','CN02381','CN02382','CN02383','CN02384','CN02385','CN02386','CN02387','CN02388','CN02389','CN02390','CN02391','CN02392','CN02393','CN02394','CN02395','CN02398','CN02399','CN02400','CN02401','CN02402','CN02403','CN02404','CN02405','CN02406','CN02407','CN02408','CN02409','CN02410','CN02412','CN02413','CN02414','CN02415','CN02416','CN02417','CN02418','CN02419','CN02420','CN02421','CN02422','CN02424','CN02426','CN02427','CN02428','CN02430','CN02432','CN02433','CN02434','CN02435','CN02436','CN02438','CN02439','CN02440','CN02441','CN02442','CN02443','CN02444','CN02445','CN02446','CN02447','CN02448','CN02450','CN02451','CN02452','CN02453','CN02455','CN02459','CN02460','CN02461','CN02462','CN02463','CN02464','CN02465','CN02466','CN02467','CN02468','CN02469','CN02470','CN02471','CN02472','CN02473','CN110112','CN18912','CN20191025','CN201975','CN20200113','CN20519','CN92834','CNO1900','CNOO308','et001'];
        $data = DB::connection('oldWarranty')->table('users')->whereNotIn('unique_id', $uid)
            ->get();
        $res = [];
        $u2 = [];

        foreach ($data as $k => $v) {
            if ($v->user_id == 1) {
                continue;
            }
            $res[] = [
                'unique_id' => $v->unique_id,
                'first_name' => $v->first_name,
                'unique_id' => $v->unique_id,
            ];
        }
        return [$data];
        foreach ($data as $k => $v) {
            $u2[] = $v->unique_id;
        }
        $res = array_diff($user, $u2);

        $data = [];
        /*$user = ['CN00001','CN00002','CN00003','CN00006','CN00009','CN00033','CN00039','CN00040','CN02221','CN00017','CN00028','CN00037','CN00016','CN00005','CN00014','CN00004','CN000019','CN00023','CN00026','CN00627','CN00029','CN00031','CN00032','CN00034','CN00036','CN00038','CN00042','CN00043','CN00044','CN00046','CN00047','CN00048','CN02144'];
        $data = DB::connection('oldWarranty')->table('users')->whereIn('unique_id', $user)
            ->get();
        $res = [];
        $u2 = [];
        foreach ($data as $k => $v) {
            $u2[] = $v->unique_id;
        }
        $res = array_diff($user, $u2);*/
        $dealer = ['CN00160','CN00160','CN00234','CN00234','CN00867','CN00867','CN01104','CN01104','CN01137','CN01137','CN01281','CN01281','CN01620','CN01620','CN01668','CN01668','CN01691','CN01691','CN01806','CN01806','CN01875','CN01875','CN01889','CN01889','CN02123','CN02123','CN02429','CN02429','CN02454','CN02454','123456','CN00045','CN01294','CN00674','CN02008','ASP025','CN00016','CN00018','CN00023','CN00026','CN00029','CN00032','CN00038','CN00045','CN00065','CN00075','CN00082','CN00083','CN00090','CN00093','CN00094','CN00105','CN00108','CN0011','CN00158','CN00159','CN00169','CN00177','CN00182','CN00184','CN00186','CN00189','CN00201','CN00218','CN00226','CN00243','CN00244','CN00245','CN00255','CN00256','CN00263','CN00276','CN00284','CN00311','CN00322','CN00325','CN00326','CN00327','CN00347','CN00356','CN00359','CN00361','CN00362','CN00363','CN00368','CN00369','CN00377','CN00378','CN00380','CN00386','CN00391','CN00395','CN00402','CN00403','CN00408','CN00409','CN00414','CN00415','CN00417','CN00426','CN00427','CN00428','CN00447','CN00450','CN00456','CN00461','CN00465','CN00469','CN00474','CN00476','CN00484','CN00485','CN00488','CN00491','CN00492','CN00493','CN00498','CN00500','CN00502','CN00503','CN00507','CN00512','CN00514','CN00524','CN00534','CN00538','CN00541','CN00546','CN00548','CN00550','CN00552','CN00554','CN00558','CN00560','CN00561','CN00566','CN00569','CN00580','CN00584','CN00586','CN00594','CN00595','CN00601','CN00602','CN00605','CN00606','CN00617','CN00621','CN00625','CN00626','CN00628','CN00630','CN00632','CN00637','CN00638','CN00643','CN00644','CN00647','CN00650','CN00655','CN00656','CN00659','CN006591','CN00660','CN00661','CN00662','CN00664','CN00668','CN00669','CN00673','CN00676','CN00677','CN00680','CN00683','CN00690','CN00691','CN00692','CN00693','CN00694','CN00695','CN00696','CN00697','CN00700','CN00701','CN00702','CN00704','CN00707','CN00714','CN00715','CN00721','CN00724','CN00725','CN00727','CN00728','CN00729','CN00731','CN00733','CN00734','CN00735','CN00736','CN00738','CN00740','CN00741','CN00745','CN00746','CN00749','CN00750','CN00751','CN00761','CN00766','CN00770','CN00783','CN00787','CN00788','CN00789','CN00792','CN00793','CN00800','CN00803','CN00806','CN00808  ','CN00809','CN00812','CN00813','CN00817','CN00819','CN00820','CN00823','CN00827','CN00832','CN00842','CN00844','CN00845','CN00847','CN00852','CN00854','CN00855','CN00856','CN00860','CN00862','CN00864','CN00866','CN00868','CN00869','CN00870','CN00873','CN00875','CN00881','CN00882','CN00883','CN00889','CN00892','CN00897','CN00899','CN00900','CN00904','CN00909','CN00910','CN00913','CN00914','CN00915','CN00919','CN00924','CN00925','CN00928','CN00929','CN00935','CN00937','CN00938','CN00939','CN00941','CN00943','CN00946','CN00949','CN00951','CN00952','CN00960','CN00961','CN00962','CN00963','CN00964','CN009656','CN00967','CN00968','CN00976','CN00982','CN00984','CN00986','CN00989','CN00993','CN00997','CN00998','CN01000','CN01002','CN01003','CN01004','CN01008','CN01011','CN01014','CN01016','CN01018','CN01020','CN01021','CN01022','CN01023','CN01027','CN01029','CN01031','CN01034','CN01036','CN01037','CN01038','CN01039','CN01042','CN01044','CN01049','CN01050','CN01051','CN01055','CN01058','CN01059','CN01060','CN01061','CN01062','CN01065','CN01066','CN01067','CN01068','CN01069','CN01077','CN01079','CN01080','CN01085','CN01090','CN01091','CN01093','CN01094','CN01096','CN01097','CN01098','CN01099','CN01103','CN01105','CN01106','CN01107','CN01109','CN01110','CN01111','CN01113','CN01114','CN01119','CN01121','CN01122','CN01123','CN01124','CN01128','CN01130','CN01131','CN01132','CN01133','CN01135','CN01136','CN01138','CN01141','CN01142','CN01143','CN01149','CN01152','CN01153','CN01154','CN01155','CN01156','CN01159','CN01160','CN01161','CN01163','CN01164','CN01165','CN01170','CN01178','CN01179','CN01182','CN01184','CN01185','CN01189','CN01190','CN01191','CN01192','CN01193','CN01194','CN01195','CN01196','CN01198','CN01200','CN01203','CN01204','CN01207','CN01209','CN01211','CN01212','CN01213','CN01215','CN01216','CN01217','CN01219','CN01220','CN01225','CN01226','CN01227','CN01228','CN01229','CN01230','CN01231','CN01235','CN01236','CN01240','CN01242','CN01243','CN01256','CN01257','CN01260','CN01261','CN01263','CN01264','CN01265','CN01266','CN01270','CN01271','CN01272','CN01275','CN01279','CN01280','CN01285','CN01286','CN01287','CN01288','CN01290','CN01291','CN01292','CN01295','CN01297','CN01298','CN01300','CN01301','CN01302','CN01303','CN01304','CN01305','CN01307','CN01308','CN01309','CN01310','CN01311','CN01312','CN01313','CN01316','CN01318','CN01322','CN01327','CN01328','CN01332','CN01334','CN01335','CN01337','CN01338','CN01339','CN01344','CN01345','CN01346','CN01347','CN01348','CN01349','CN01350','CN01351','CN01352','CN01353','CN01355','CN01356','CN01358','CN01366','CN01367','CN01368','CN01370','CN01371','CN01373','CN01375','CN01378','CN01379','CN01381','CN01384','CN01386','CN01388','CN01389','CN01391','CN01392','CN01396','CN01398','CN01399','CN01402','CN01403','CN01404','CN01406','CN01411','CN01412','CN01413','CN01415','CN01416','CN01417','CN01419','CN01421','CN01422','CN01424','CN01425','CN01426','CN01429','CN01430','CN01451','CN01453','CN01454','CN01455','CN01456','CN01458','CN01459','CN01463','CN01464','CN01465','CN01468','CN01469','CN01470','CN01471','CN01475','CN01479','CN01484','CN01485','CN01486','CN01487','CN01488','CN01490','CN01493','CN01494','CN01495','CN01500','CN01501','CN01502','CN01505','CN01508','CN01509','CN01510','CN01511','CN01512','CN01514','CN01515','CN01516','CN01518','CN01521','CN01522','CN01524','CN01526','CN01527','CN01528','CN01529','CN01531','CN01532','CN01534','CN01535','CN01537','CN01539','CN01540','CN01541','CN01543','CN01544','CN01545','CN01546','CN01547','CN01548','CN01551','CN01552','CN01553','CN01554','CN01555','CN01556','CN01557','CN01560','CN01562','CN01563','CN01566','CN01567','CN01568','CN01569','CN01570','CN01571','CN01572','CN01575','CN01577','CN01579','CN01580','CN01581','CN01584','CN01585','CN01588','CN01589','CN01591','CN01595','CN01596','CN01597','CN01601','CN01602','CN01603','CN01604','CN01605','CN01606','CN01611','CN01612','CN01613','CN01615','CN01616','CN01617','CN01618','CN01619','CN01621','CN01622','CN01623','CN01624','CN01625','CN01626','CN01627','CN01629','CN01630','CN01631','CN01632','CN01634','CN01635','CN01636','CN01638','CN01640','CN01641','CN01645','CN01646','CN01649','CN01650','CN01652','CN01653','CN01654','CN01655','CN01656','CN01657','CN01659','CN01662','CN01663','CN01666','CN01667','CN01669','CN01670','CN01671','CN01672','CN01677','CN01680','CN01681','CN01683','CN01684','CN01685','CN01686','CN01687','CN01689','CN01692','CN01693','CN01694','CN01696','CN01700','CN01703','CN01704','CN01706','CN01708','CN01709','CN01710','CN01712','CN01713','CN01714','CN01715','CN01716','CN01717','CN01718','CN01719','CN01720','CN01721','CN01724','CN01725','CN01726','CN01727','CN01728','CN01729','CN01733','CN01734','CN01736','CN01738','CN01739','CN01740','CN01741','CN01743','CN01745','CN01746','CN01747','CN01749','CN01750','CN01752','CN01754','CN01755','CN01756','CN01758','CN01759','CN01760','CN01761','CN01762','CN01763','CN01764','CN01766','CN01767','CN01769','CN01770','CN01772','CN01773','CN01774','CN01775','CN01778','CN01779','CN01780','CN01781','CN01782','CN01786','CN01787','CN01789','CN01790','CN01792','CN01793','CN01794','CN01795','CN01796','CN01807','CN01808','CN01809','CN01810','CN01811','CN01812','CN01813','CN01814','CN01817','CN01818','CN01819','CN01820','CN01821','CN01822','CN01823','CN01824','CN01826','CN01827','CN01828','CN01829','CN01830','CN01831','CN01832','CN01836','CN01839','CN01840','CN01841','CN01842','CN01843','CN01844','CN01845','CN01847','CN01849','CN01850','CN01851','CN01852','CN01854','CN01855','CN01856','CN01857','CN01858','CN01860','CN01861','CN01863','CN01865','CN01869','CN01870','CN01872','CN01874','CN01876','CN01877','CN01878','CN01879','CN01880','CN01881','CN01882','CN01886','CN01887','CN01891','CN01895','CN01897','CN01898','CN01899','CN01900','CN01902','CN01903','CN01904','CN01905','CN01907','CN01908','CN01909','CN01911','CN01912','CN01916','CN01919','CN01922','CN01923','CN01924','CN01925','CN01928','CN01929','CN01930','CN01931','CN01934','CN01938','CN01940','CN01941','CN01942','CN01943','CN01944','CN01945','CN01948','CN01950','CN01951','CN01952','CN01953','CN01955','CN01956','CN01957','CN01958','CN01959','CN01960','CN01961','CN01962','CN01963','CN01964','CN01965','CN01966','CN01967','CN01968','CN01969','CN01971','CN01972','CN01973','CN01974','CN01975','CN01976','CN01977','CN01981','CN01983','CN01984','CN01987','CN01988','CN01989','CN01990','CN01991','CN01992','CN01993','CN01994','CN01995','CN01999','CN02000','CN02001','CN02002','CN02003','CN02004','CN02006','CN02007','CN02009','CN02012','CN02013','CN02014','CN02016','CN02017','CN02019','CN02020','CN02021','CN02023','CN02024','CN02025','CN02026','CN02027','CN02028','CN02029','CN02034','CN02035','CN02036','CN02037','CN02038','CN02039','CN02040','CN02042','CN02043','CN02044','CN02045','CN02046','CN02047','CN02048','CN02049','CN02050','CN02051','CN02052','CN02053','CN02054','CN02055','CN02056','CN02057','CN02058','CN02059','CN02060','CN02061','CN02062','CN02063','CN02064','CN02065','CN02066','CN02067','CN02068','CN02069','CN02070','CN02071','CN02072','CN02073','CN02074','CN02075','CN02076','CN02077','CN02078','CN02079','CN02080','CN02081','CN02082','CN02083','CN02084','CN02085','CN02086','CN02087','CN02088','CN02089','CN02090','CN02091','CN02092','CN02094','CN02095','CN02096','CN02097','CN02098','CN02099','CN02100','CN02101','CN02102','CN02103','CN02104','CN02105','CN02106','CN02107','CN02108','CN02109','CN02110','CN02111','CN02112','CN02113','CN02114','CN02115','CN02116','CN02117','CN02118','CN02119','CN02120','CN02121','CN02122','CN02124','CN02125','CN02126','CN02127','CN02128','CN02129','CN02130','CN02131','CN02132','CN02133','CN02134','CN02135','CN02136','CN02137','CN02138','CN02141','CN02142','CN02143','CN02145','CN02146','CN02147','CN02148','CN02149','CN02150','CN02151','CN02152','CN02153','CN02154','CN02155','CN02156','CN02157','CN02158','CN02159','CN02160','CN02161','CN02162','CN02163','CN02164','CN02165','CN02166','CN02167','CN02168','CN02169','CN02170','CN02172','CN02173','CN02174','CN02175','CN02176','CN02177','CN02178','CN02179','CN02180','CN02182','CN02183','CN02184','CN02185','CN02186','CN02187','CN02188','CN02189','CN02190','CN02191','CN02192','CN02193','CN02194','CN02196','CN02197','CN02199','CN02200','CN02201','CN02202','CN02203','CN02204','CN02205','CN02206','CN02207','CN02208','CN02209','CN02210','CN02211','CN02212','CN02213','CN02214','CN02215','CN02216','CN02217','CN02218','CN02219','CN02220','CN02222','CN02223','CN02224','CN02225','CN02226','CN02227','CN02228','CN02229','CN02230','CN02231','CN02232','CN02233','CN02234','CN02235','CN02237','CN02238','CN02239','CN02240','CN02241','CN02242','CN02243','CN02244','CN02246','CN02247','CN02248','CN02249','CN02250','CN02251','CN02252','CN02253','CN02254','CN02256','CN02257','CN02258','CN02259','CN02260','CN02261','CN02262','CN02263','CN02264','CN02265','CN02266','CN02267','CN02268','CN02269','CN02270','CN02271','CN02272','CN02273','CN02274','CN02276','CN02277','CN02278','CN02279','CN02280','CN02286','CN02288','CN02291','CN02296','CN02297','CN02299','CN02300','CN02301','CN02302','CN02303','CN02304','CN02305','CN02306','CN02307','CN02308','CN02309','CN02310','CN02311','CN02312','CN02313','CN02314','CN02315','CN02316','CN02317','CN02318','CN02319','CN02320','CN02321','CN02322','CN02324','CN02325','CN02326','CN02327','CN02328','CN02329','CN02330','CN02331','CN02332','CN02333','CN02334','CN02335','CN02336','CN02337','CN02338','CN02339','CN02340','CN02341','CN02342','CN02343','CN02344','CN02345','CN02346','CN02347','CN02348','CN02349','CN02350','CN02351','CN02353','CN02354','CN02355','CN02357','CN02358','CN02359','CN02360','CN02361','CN02362','CN02363','CN02364','CN02365','CN02366','CN02367','CN02368','CN02369','CN02370','CN02371','CN02372','CN02373','CN02374','CN02376','CN02377','CN02378','CN02379','CN02381','CN02382','CN02383','CN02384','CN02385','CN02386','CN02387','CN02388','CN02389','CN02390','CN02391','CN02392','CN02393','CN02394','CN02395','CN02398','CN02399','CN02400','CN02401','CN02402','CN02403','CN02404','CN02405','CN02406','CN02407','CN02408','CN02409','CN02410','CN02412','CN02413','CN02414','CN02415','CN02416','CN02417','CN02418','CN02419','CN02420','CN02421','CN02422','CN02424','CN02426','CN02427','CN02428','CN02430','CN02432','CN02433','CN02434','CN02435','CN02436','CN02438','CN02439','CN02440','CN02441','CN02442','CN02443','CN02444','CN02445','CN02446','CN02447','CN02448','CN02450','CN02451','CN02452','CN02453','CN02455','CN02459','CN02460','CN02461','CN02462','CN02463','CN02464','CN02465','CN02466','CN02467','CN02468','CN02469','CN02470','CN02471','CN02472','CN02473','CN110112','CN18912','CN20191025','CN201975','CN20200113','CN20519','CN92834','CNO1900','CNOO308','et001'];
        $dealer = array_unique($dealer);
        foreach ($dealer as $k => $v) {
            $dealer[$k] = strtoupper(trim($v));
        }
        $data = DB::connection('oldWarranty')->table('users')->whereIn('unique_id', $dealer)
            ->get();
        $res = [];
        $u2 = [];
        $ots = [];
        foreach ($data as $k => $v) {
            if ($v->user_type != 3 ) {
                $other[] = strtoupper(trim($v->unique_id));
            }
            $unique_id = strtoupper(trim($v->unique_id));
            $u2[] = $unique_id;
            if (in_array($unique_id, ['CN00018'])) {
                $ots[] = $v;
            }
        }
        $res = array_diff($dealer, $u2);
        return [array_values($res), $other, count($data), count(array_unique($dealer)), $ots];
        // $data = DB::connection('oldWarranty')->table('roll_sales')->count();

        /*$precut = DB::connection('oldWarranty')->table('precut_kit')->get();
        $precut = DB::connection('oldWarranty')->table('precut_kit')->get();*/

        return [$data];

        return [123];

    }

    public function getPVC($limit, $offset)
    {
        $precut = DB::connection('oldWarranty')->table('precut_kit')
            ->limit($limit)
            ->offset($offset)
            ->get();

        foreach (range(1,50) as $v) {
            // $data = $this->insert_dwz2();
            $data = $this->dwzt3();
            $string = json_encode($data);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO aadwz_3 (user_id, dwz, url, created_at, updated_at) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
    }


    public function new1105()
    {
        $region = $province = $city = [];
        $data = DB::connection('newWarranty')->table('region_master')->get();
        foreach ($data as $k => $v) {
            $region[$v->id] = $v->simplified_chiness_value;
        }
        $data = DB::connection('newWarranty')->table('province_master')->get();
        foreach ($data as $k => $v) {
            $province[$v->id] = $v->simplified_chiness_value;
        }
        $data = DB::connection('newWarranty')->table('cities')->get();
        foreach ($data as $k => $v) {
            $city[$v->id] = $v->simplified_chiness_value;
        }


        $data = DB::connection('oldWarranty')->table('users')->get();
        $user = $cu1 = [];
        foreach ($data as $k => $v) {
            $cu1[$v->user_id] = $v->company_name;
        }
        $uts = [
            '',
            '管理员',
            '省代',
            '经销商'
        ];
        $field =  [
            'user_id' => '用户ID',
            'user_type' => '用户类型',
            'unique_id' => '用户账号',
            'company_name' => '公司名称',
            'creator_name' => '省代名称',
            'phone_number' => '电话号码',
            'email_address' => '邮箱地址',
            'region' => '地区',
            'province' => '省',
            'city' => '城市',
            'address' => '地址',
            'first_name' => '联系人',
            'cell_phone' => '联系电话',
            'wechat_id' => '微信号',
        ];
        foreach ($data as $k=>$v) {
            $item = $v;
            if ($v->user_type == 3) {
                $item->creator_name = @$cu1[$v->creator_id];
            } else {
                $item->creator_name = '';
            }
            $item->user_type = @$uts[$v->user_type];
            $item->phone_number = '' . $v->extension . ' ' . $v->phone_number;
            $item->region = @$region[$v->region_id];
            $item->province = @$province[$v->province_id];
            $item->city = @$city[$v->city];
            $obj = [];
            foreach ($field as $d =>$e ) {
                $obj[] = [
                    'label' => $e,
                    'value' => $item->$d
                ];
            }
            $user[] = $obj;
        }

        // ['label', 'value'];  // excel

        /*$data = DB::connection('newWarranty')->table('cities')->get();
        return $data;*/
        $ow = new OfficeWork();
        $filename = storage_path('app/users.xlsx');
        $bpp = $ow->makeExcel($user, $filename);
        return $bpp;
    }


    public function qiniuyun2(Request $request)
    {

        return view('test2');
    }

    public function qiniuyun(Request $request)
    {
        $fileName = 'Bdr9ON64VJokZ86PK6LbdX7FsAnDdhuA.png';
        return [pathinfo($fileName, PATHINFO_FILENAME)];

        /*$allow = [".png", ".jpg", ".jpeg", ".gif"];
        $path = '';*/
        $file = $request->file('file');
        $cmt = $file->getClientMimeType();
        $ext = '';
        if (stripos($cmt, 'image/') === 0) {
            $ext = substr($cmt,6);
        }
        if (empty($ext)) {
            return ['status'=>false, 'msg'=>'仅支持上传图片格式'];
        } else {
            $ext = '.'.$ext;
        }
        $fileName = Str::random(32).$ext;
        $accessKey = 'rwcfuiLsWDO7UA3UopiGmGonrijfq6lPVuXfGIhh';
        $secretKey = 'DeSPE6nIa9m3ePyyJe7_deS8KulDP8FeY1Sj84zk';
        $auth = new Auth($accessKey, $secretKey);
        $bucket = 'warranty-xpel';
        $uploadMgr = new UploadManager();
        $token = $auth->uploadToken($bucket);
        list($ret, $error) = $uploadMgr->putFile($token, $fileName, $file);


        return [$fileName, $ret, $error, is_array($ret), $ret['key']];

        $path = $file->store(date('Ym'));

        return [
            $file->getType(),
            $file->getClientMimeType(), // image/png
            $file->getFileInfo(),
            @image_type_to_extension($file),
            $ext,
            stripos($cmt, 'image/')
        ];
        list($ret, $error) = $uploadMgr->putFile($token, 'key', $file);


        // $path = $file->store();

        return [$file, $path];
    }

    public function create_table()
    {
        $tname = 'xpel_logs_1026';
        if (! Schema::hasTable($tname)) {
            DB::update("CREATE TABLE xpel_logs_1026 like xpel_logs_1025");
        } else {
            // DB::update("CREATE TABLE xpel_logs_102602 as (select * from xpel_logs_1025 where id <= 1039898)");
            // DB::select("CREATE TABLE xpel_logs_102603 as (select * from xpel_logs_1025)");
            // DB::select("CREATE TABLE xpel_logs_102603 as xpel_logs_1025");
        }
        $d = DB::table('xpel_logs_1025')
            ->where('id', '>=', 1039398+80000)
            ->count();
        return [$d];
        // $d = DB::table('xpel_logs_1026')->exists();
        return [$tname];
        /*DB::select('CREATE TABLE xpel_logs_1026')
        xpel_logs_1025*/
    }

    /*
     * 卷批量导入*/
    public function rImport()
    {
        $file = public_path('import/roll_master.xlsx');
        $spreadsheet = IOFactory::load($file);
        $arrays = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $time = date('Y-m-d H:i:s');
        $datas = [];
        foreach ($arrays as $k => $v) {
            if ($k == 1) {
                continue;
            }
            $datas[] = [
                'roll_number' => trim($v['A']),
                'film_type_id' => (int) $v['B'],
                'width' => (int) $v['C'],
                'height' => (int) $v['D'],
                'created_at' => $time,
                'updated_at' => $time,
            ];
        }
        $roll_number = Arr::pluck($datas, 'roll_number');
        $ed = DB::connection('oldWarranty')->table('roll_master')->whereIn('roll_number', $roll_number)->get(['roll_number']);
        $ex = Arr::pluck($ed, 'roll_number');
        $uploads = [];
        foreach ($datas as $k => $v) {
            if (empty($v['roll_number'])) {
                continue;
            }
            if (in_array($v['roll_number'], $ex)) {
                $v['exist'] = true;
                $datas[$k] = $v;
            } else {
                $uploads[] = $v;
            }
        }

        return view('', ['data'=> $datas]);
    }

    /*
     * 初始化销售表*/
    public function iRSales()
    {
        $data = DB::connection('oldWarranty')->table('roll_sales')
        ;


    }

    /*
     * 初始化剩余长度表
     * 获取剩余长度*/
    public function getRestlen()
    {


    }

    /*
     * 初始化静态资源、简单内容
     * 初始化静态文本
     * 包含地区、省份、城市、膜的种类、品牌*/
    public function iStaticText()
    {
        /*$text = '&#25105&#30340&#31616&#21382';
        if (mb_stripos($text, '&#')===0) {
            $text = str_replace('&#', ';&#', $text).';';
            $text = mb_substr($text, 1);
        }
        $text = html_entity_decode($text);
        return ['&#25105&#30340&#31616&#21382', $text];*/
        $table = ['cities','province_master','region_master','macro_management','product_types','make_master','part_master','film_type_master'];
        $res = [];
        $res['cities'] = $this->iCities();
        $res['province_master'] = $this->iProvince();
        $res['region_master'] = $this->iRegion();
        $res['macro_management'] = $this->iMacro();
        $res['product_types'] = $this->iMacro();
        $res['make_master'] = $this->iMake();
        $res['part_master'] = $this->iPart();
        $res['film_type_master'] = $this->iFilm();
        $res['phone_extension'] = $this->iPExt();
        $res['sequences'] = $this->iSeq();
        return $res;
        // $datas = DB::connection('newWarranty')->table('cities')->insert($uploads);

        return $uploads;
    }
    /*
     * 表自增ID
     * 主要为套件ID*/
    public function iSeq()
    {
        $datas = DB::connection('oldWarranty')->table('sequences')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $uploads[] = [
                'table' => $v->table,
                'next' => $v->next,
                'created_at' => $time,
                'updated_at' => $time,
            ];
        }
        $bool = null;
        $tname = 'sequences';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 电话区号*/
    public function iPExt()
    {
        $time = date('Y-m-d H:i:s');
        $uploads = [
            [
                'id' => 1,
                'phone_ext' => '+86',
                'created_at' => $time,
                'updated_at' => $time,
            ],
            [
                'id' => 2,
                'phone_ext' => '+886',
                'created_at' => $time,
                'updated_at' => $time,
            ],
            [
                'id' => 3,
                'phone_ext' => '+853',
                'created_at' => $time,
                'updated_at' => $time,
            ],
            [
                'id' => 4,
                'phone_ext' => '+852',
                'created_at' => $time,
                'updated_at' => $time,
            ],
        ];
        $bool = null;
        $tname = 'phone_extension';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 膜的种类*/
    public function iFilm()
    {
        $datas = DB::connection('oldWarranty')->table('film_type_master')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->film_type_id;
            $text = $v->film_type_name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'product_type_id' => $v->product_type_id,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'film_type_master';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 安装部位*/
    public function iPart()
    {
        $datas = DB::connection('oldWarranty')->table('part_master')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->part_id;
            $text = $v->part_name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            $min_length = 0;
            $select_part = $v->part_name;
            if($select_part == "Other" || $select_part == "其他（局部套餐）" || $select_part == "其它（局部套餐）"){
                $min_length = 10;
            }
            if($select_part == "Bumper" || $select_part == "前保險槓" || $select_part == "前保险杠"){
                $min_length = 140;
            }
            if($select_part == "Rear Bumper" || $select_part == "後保險槓" || $select_part == "后保险杠"){
                $min_length = 150;
            }
            if($select_part == "Hood" || $select_part == "引擎蓋" || $select_part == "机盖"){
                $min_length = 180;
            }
            if($select_part == "Fender" || $select_part == "前葉子板" || $select_part == "前叶子板"){
                $min_length = 120;
            }
            if($select_part == "Single Fender" || $select_part == "單片前葉子板" || $select_part == "單片前葉子板"){
                $min_length = 76;
            }
            if($select_part == "Door" || $select_part == "門" || $select_part == "门"){
                $min_length = 250;
            }
            if($select_part == "Single Door" || $select_part == "單個門" || $select_part == "單個門"){
                $min_length = 65;
            }
            if($select_part == "Rear Fender" || $select_part == "後葉子板" || $select_part == "后叶子板"){
                $min_length = 300;
            }
            if($select_part == "Single Rear Fender" || $select_part == "單片後葉子板" || $select_part == "單片後葉子板"){
                $min_length = 150;
            }
            if($select_part == "Roof" || $select_part == "車頂" || $select_part == "车顶"){
                $min_length = 150;
            }
            if($select_part == "Rocker" || $select_part == "側裙" || $select_part == "侧裙"){
                $min_length = 30;
            }
            if($select_part == "Door Cup" || $select_part == "手把碗公" || $select_part == "门杯"){
                $min_length = 5;
            }
            if($select_part == "Headlight" || $select_part == "大燈" || $select_part == "大灯"){
                $min_length = 25;
            }
            if($select_part == "Rear View Mirror" || $select_part == "後視鏡" || $select_part == "后视镜"){
                $min_length = 10;
            }
            if($select_part == "Trunk Lid" || $select_part == "行李箱" || $select_part == "行李箱"){
                $min_length = 80;
            }
            if($select_part == "Full Front" || $select_part == "全車頭 (引擎蓋+葉子板+前保險槓)" || $select_part == "全车头（引擎盖+叶子板+前保险杠）"){
                $min_length = 480;
            }
            if($select_part == "Full Car" || $select_part == "全車" || $select_part == "全车"){
                $min_length = 1200;
            }
            if($select_part == "Other" || $select_part == "其他" || $select_part == "其他"){
                $min_length = 10;
            }
            if($select_part == "Hood & Bumper" || $select_part == "引擎蓋+葉子板" || $select_part == "引擎盖+叶子板"){
                $min_length = 50;
            }
            if($select_part == "ASP Bikini Hood & Bumper" || $select_part == "ASP Bikini 引擎蓋+葉子板" || $select_part == "ASP Bikini 引擎蓋+葉子板"){
                $min_length = 50;
            }
            if($select_part == "B-Pillar" || $select_part == "B柱" || $select_part == "B柱"){
                $min_length = 10;
            }
            if($select_part == "ASP Interior Kit" || $select_part == "ASP 內飾套餐" || $select_part == "ASP 内饰套餐"){
                $min_length = 0;
            }
            if($select_part == "ASP Partial Kit" || $select_part == "ASP 局部套餐" || $select_part == "ASP 局部套餐"){
                $min_length = 0;
            }
            if($select_part == "General Interior Kit" || $select_part == "一般內飾套餐" || $select_part == "一般内饰套餐"){
                $min_length = 80;
            }
            if($select_part == "Steering Column Cover" || $select_part == "土除" || $select_part == "土除"){
                $min_length = 10;
            }
            if($select_part == "Front Shield of Vehicle frame" || $select_part == "斜板" || $select_part == "斜板"){
                $min_length = 10;
            }
            if($select_part == "Engine Cowl and vehicle Frame-Both sides" || $select_part == "左右車身" || $select_part == "左右车身"){
                $min_length = 600;
            }
            if($select_part == "Handlebar Housing and dashboard" || $select_part == "車首蓋" || $select_part == "车首盖"){
                $min_length = 180;
            }
            if($select_part == "Side Step Cover-Both Sides" || $select_part == "左右關刀" || $select_part == "左右关刀"){
                $min_length = 10;
            }
            if($select_part == "Font Storage" || $select_part == "手套箱" || $select_part == "手套箱"){
                $min_length = 10;
            }
            if($select_part == "Font Steps" || $select_part == "手套箱下方" || $select_part == "手套箱下方"){
                $min_length = 10;
            }
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'min_length' => $min_length,
                    'product_type_id' => $v->product_type_id,
                    'visibility' => $v->visibility,
                    'order' => $v->order,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'part_master';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 车型*/
    public function iMake()
    {
        $datas = DB::connection('oldWarranty')->table('make_master')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->make_id;
            $text = $v->make_name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'year_id' => $v->year_id,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'make_master';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 产品种类*/
    public function iProduct()
    {
        $datas = DB::connection('oldWarranty')->table('product_types')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->id;
            $text = $v->name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'product_types';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 静态文本*/
    public function iMacro()
    {
        $datas = DB::connection('oldWarranty')->table('macro_management')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->macro_id;
            $zt = trim($v->traditional_chiness_value);
            $zs = trim($v->simplified_chiness_value);
            if (mb_stripos($zt, '&#')===0) {
                $zt = str_replace('&#', ';&#', $zt).';';
                $zt = mb_substr($zt, 1);
            }
            if (mb_stripos($zs, '&#')===0) {
                $zs = str_replace('&#', ';&#', $zs).';';
                $zs = mb_substr($zs, 1);
            }

            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'macro_name' => 'macro_name',
                    'created_at' => $time,
                    'updated_at' => $time,
                    'english_value' => trim($v->english_value),
                    'traditional_chiness_value' => html_entity_decode($zt),
                    'simplified_chiness_value' => html_entity_decode($zs),
                ];
            }
        }
        $bool = null;
        $tname = 'macro_management';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 地区*/
    public function iRegion()
    {
        $datas = DB::connection('oldWarranty')->table('region_master')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->region_id;
            $text = $v->region_name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'region_master';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 省份*/
    public function iProvince()
    {
        $datas = DB::connection('oldWarranty')->table('province_master')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->province_id;
            $text = $v->province_name;
            if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'region_id' => $v->region_id,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'province_master';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    /*
     * 城市*/
    public function iCities()
    {
        $datas = DB::connection('oldWarranty')->table('cities')->get();
        $uploads = [];
        $time = date('Y-m-d H:i:s');
        foreach ($datas as $k => $v) {
            $key = $v->city_id;
            $text = $v->city;
            /*if (mb_stripos($text, '&#')===0) {
                $text = str_replace('&#', ';&#', $text).';';
                $text = mb_substr($text, 1);
            }*/
            $text = html_entity_decode($text);
            if (empty($uploads[$key])) {
                $uploads[$key] = [
                    'id' => $key,
                    'province_id' => $v->province_id,
                    'created_at' => $time,
                    'updated_at' => $time,
                ];
            }
            if ($v->language_id == 1) {
                $uploads[$key]['english_value'] = $text;
            } elseif ($v->language_id == 2) {
                $uploads[$key]['traditional_chiness_value'] = $text;
            } elseif ($v->language_id == 3) {
                $uploads[$key]['simplified_chiness_value'] = $text;
            }
        }
        $bool = null;
        $tname = 'cities';
        $count = DB::connection('newWarranty')->table($tname)->count();
        if (!$count) {
            $bool = DB::connection('newWarranty')->table($tname)->insert($uploads);
        }
        return [$tname=>$bool];
    }
    public function mLan($v, &$data, $column, $key)
    {
        $text = html_entity_decode($v->$column);
        if ($v->language_id == 1) {
            $data[$v->city_id]['english_value'] = $text;
        } elseif ($v->language_id == 2) {
            $data[$v->city_id]['traditional_chiness_value'] = $text;
        } elseif ($v->language_id == 3) {
            $data[$v->city_id]['simplified_chiness_value'] = $text;
        }
    }

    public function mSql($ins, $datas)
    {
        $string = json_encode($datas);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        return $ins.$string."; \n";
    }




    public function sms1()
    {
        $a = 'xingyinghy';
        $k = 'cabc28e874dc0eff98f19d2d57ac9a36';

        $url = 'http://api.ljioe.cn/api/v1/sms';
        $mobile = '18817354090';
        $account = 'xingyinghy';
        $timestamp = time();
        $key = md5($k.md5($timestamp));
        $year_id = '测试年份2019';
        $make = '测试品牌';
        $model = '测试车型';
        $content = '【XPEL保护膜认证店】尊敬的客户：谢谢您选择XPEL超跑级保护膜，您的['.$year_id.' '.$make.' '.$model.']已经成功上传全国统一的电子质保系统。您可以在微信的公众平台搜索“XPEL”并查询真伪。如有问题请咨询XPEL特约授权经销商。http://www.xpel.com.cn/';;
        $params = [
            'content' => $content,
            'mobile' => $mobile,
            'account' => $account,
            'code' => '',
            'ext' => '',
            'timestamp' => $timestamp,
            'key' => $key,
        ];
        $client = new Client();
        // $url = 'https://www.nanqi.org/nanqibaojia/json/baojia';
        $res = $client->request('post', $url, ['json' => $params]);
        $data = $res->getBody()->getContents();
        if (is_string($data)) {
            $obj = json_decode($data);
        } else {
            $obj = $data;
        }
        return [$obj];
        return [is_string($data), $obj, $params];
    }


    public function dwz2()
    {
        $data = [];
        foreach (range(0,100) as $v) {
            $data[] = Str::random(8);
        }
        return $data;
    }

    public function shell()
    {
        $shell = "ls -la";
        echo "<pre>";
        system($shell, $status);
        echo "</pre>";
        $shell = "<span color='red'>$shell</span>";
        if( $status ){
            echo "shell命令{$shell}执行失败";
        } else {
            echo "shell命令{$shell}成功执行";
        }
        // return [$status];
    }

    public function insert_dwz()
    {
        $data = [];
        foreach (range(0,50000) as $v) {
            $item = [
                'user_id' => rand(0, 10000),
                'dwz' => Str::random(8),
                'url' => 'http://www.phi.cn/dwz/'.Str::random(rand(8, 64)),
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
            ];
            $data[] = $item;
        }
        DB::table('aadwz')->insert($data);
    }
    public function insert_dwz2()
    {
        $data = [];
        $t = date('Y-m-d H:i:s');
        foreach (range(0,100000) as $v) {
            $item = [
                'user_id' => rand(0, 10000),
                'dwz' => Str::random(8),
                'url' => 'http://www.phi.cn/dwz/'.Str::random(32),
                'created_at' => $t,
                'updated_at' => $t,
            ];
            $data[] = array_values($item);
        }
       /* $string = json_encode($data);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);
        $filename = public_path('dwz/'.time().'.sql', $string);
        $ins = "INSERT INTO aadwz (user_id, dwz, url, created_at, updated_at) VALUES \n";
        file_put_contents($filename, $ins.$string);*/
        return $data;
    }
    public function random($length = 16)
    {
        $string = '';
        while (($len = strlen($string)) < $length) {
            $size = $length - $len;
            $bytes = random_bytes($size);
            $string .= substr(str_replace(['/', '+', '='], '', base64_encode($bytes)), 0, $size);
        }
        return $string;
    }
    
    public function dwzt1()
    {
        // $t = date('Y-m-d H:i:s');
        foreach (range(1,1000000) as $v) {
            $t = date('Y-m-d H:i:s');
            /*$item = [
                'user_id' => rand(0, 10000),
                'dwz' => Str::random(8),
                'url' => 'http://www.phi.cn/dwz/'.Str::random(32),
                'created_at' => $t,
                'updated_at' => $t,
            ];*/
            // $data[] = Str::random(32);
            // $data[] = $this->random(32);
            // $data[] = rand(8, 64);
        }
    }
    public function dwzt2()
    {
        $data = [];
        $t = date('Y-m-d H:i:s');
        foreach (range(0,100000) as $v) {
            $item = [
                'user_id' => rand(0, 10000),
                'dwz' => Str::random(8),
                'url' => 'http://www.phi.cn/dwz/'.Str::random(32),
                'created_at' => $t,
                'updated_at' => $t,
            ];
            $data[] = array_values($item);
        }
        return $data;
    }
    public function dwzt3()
    {
        $data = [];
        $t = date('Y-m-d H:i:s');
        for ($i=0;$i<=100000;$i++) {
            $item = [
                'user_id' => rand(0, 10000),
                'dwz' => Str::random(8),
                'url' => 'http://www.phi.cn/dwz/'.Str::random(32),
                'created_at' => $t,
                'updated_at' => $t,
            ];
            $data[] = array_values($item);
        }
        return $data;
    }
    /*
     * 追加写入
     * 每次写入多条文件*/
    public function dwz_init4()
    {
        /*$e = microtime(1);
        $sql = '';
        foreach (range(1, 50) as $v) {
            $data = $this->dwzt3();
            $string = json_encode($data);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO aadwz_2 (user_id, dwz, url, created_at, updated_at) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        return [microtime(1) - $e];*/
        $filename = public_path('dwz/'.time().'.sql');
        $sql = '';
        foreach (range(1,1) as $i) {
            foreach (range(1,50) as $v) {
                // $data = $this->insert_dwz2();
                $data = $this->dwzt3();
                $string = json_encode($data);
                $search = ['[[', '[', ']]', ']'];
                $repace = ['(', '(', ')', ')'];
                $string = str_replace($search, $repace, $string);
                $ins = "INSERT INTO aadwz_3 (user_id, dwz, url, created_at, updated_at) VALUES \n";
                $sql .= $ins.$string."; \n";
            }
            file_put_contents($filename, $sql, FILE_APPEND); // 如果文件已存在，追加数据
            $sql = '';
        }
    }

    /*
     * 追加写入*/
    public function dwz_init3()
    {
        $filename = public_path('dwz/'.time().'.sql');
        $sql = '';
        foreach (range(1,500) as $v) {
            $data = $this->insert_dwz2();
            $string = json_encode($data);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO aadwz_2 (user_id, dwz, url, created_at, updated_at) VALUES \n";
            $sql .= $ins.$string."; \n";
            file_put_contents($filename, $sql, FILE_APPEND); // 如果文件已存在，追加数据
            $sql = '';
        }
    }

    public function dwz_init2()
    {
        $sql = '';
        foreach (range(1,100) as $v) {
            $data = $this->insert_dwz2();
            $string = json_encode($data);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $ins = "INSERT INTO aadwz_2 (user_id, dwz, url, created_at, updated_at) VALUES \n";
            $sql .= $ins.$string."; \n";
        }
        $filename = public_path('dwz/'.time().'.sql'); // $string
        file_put_contents($filename, $sql);
    }
    // FILE_APPEND

    public function dwz_init(Request $request)
    {
        \Debugbar::disable();
        $e = time();
        // $data = $this->dwz_init2();
        $data = $this->dwz_init4();
        /*
        $string = json_encode($data);
        $search = ['[[', '[', ']]', ']'];
        $repace = ['(', '(', ')', ')'];
        $string = str_replace($search, $repace, $string);*/
        // return $string;
        return [time()-$e];
    }

    function parseLogLine($line) {
        $item = [];
        $p = '/^(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\s-\s(.*)\s\[(.*)\]\s"(.*)\"\s(\d{3})\s(\d+)\s"(.*)"\s\"(.*)\"(.*)$/u';
        preg_match($p,$line,$match);
        $l2 = explode(' ', $match[4], 3);
        $time = date('Y-m-d H:i:s', strtotime($match[3]));
        $method = substr(@$l2[0], 0, 16);
        $method = str_replace("'", "\'", $method);
        $item = [
            'ip' => $match[1],
            'time' => $time,
            'method' => $method,
            'url' => substr(@$l2[1], 0, 198), //str_replace("'", "\'", @$l2[1]),
            'status' => (int) $match[5],
            'referer' => @$match[7], // str_replace("'", "\'", @$match[7]),
            'agent' => @$match[8], // str_replace("'", "\'", @$match[8]),
            'log' => @$line,// str_replace("'", "\'", @$line),
        ];
        return $item;
    }
    public function logs1025()
    {
        $e = time();
        $filename = public_path('1025/access.log');
        $handle = @fopen($filename, 'r');
        $datas = [];
        $string = '';
        $i = 1;
        $sql = '';
        $ins = "INSERT INTO xpel_logs_1025 (`ip`, `time`, `method`, `url`, `status`, `referer`, `agent`, `log`) VALUES \n";
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $item = $this->parseLogLine($buffer);
                $s = array_values($item);
                $datas[] = $s;
                if (empty($i % 10000)) {
                    $string = json_encode($datas);
                    $search = ['[[', '[', ']]', ']'];
                    $repace = ['(', '(', ')', ')'];
                    $string = str_replace($search, $repace, $string);
                    $sql .= $ins.$string."; \n";
                    $datas = [];
                }
                $i++;
            }
            if (!feof($handle)) {}
            fclose($handle);
        }
        if (!empty($datas)) {
            $string = json_encode($datas);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $sql .= $ins.$string."; \n";
        }
        file_put_contents(public_path('dist/xpel_logs_1025.sql'), $sql);
        // var_dump(count($datas));
        var_dump(time() - $e);

    }
    public function parse_logs4()
    {
        $e = time();
        $filename = public_path('dist/a-access-2010211248.log');
        $handle = @fopen($filename, 'r');
        $datas = [];
        $string = '';
        $i = 1;
        $sql = '';
        $ins = "INSERT INTO xpel_logs_102202 (`ip`, `time`, `method`, `url`, `status`, `referer`, `agent`, `log`) VALUES \n";
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $item = $this->parseLogLine($buffer);
                $s = array_values($item);
                $datas[] = $s;
                if (empty($i % 10000)) {
                    $string = json_encode($datas);
                    $search = ['[[', '[', ']]', ']'];
                    $repace = ['(', '(', ')', ')'];
                    $string = str_replace($search, $repace, $string);
                    $sql .= $ins.$string."; \n";
                    $datas = [];
                }
                $i++;
            }
            if (!feof($handle)) {}
            fclose($handle);
        }
        if (!empty($datas)) {
            $string = json_encode($datas);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $sql .= $ins.$string."; \n";
        }
        file_put_contents(public_path('dist/xpel_logs_102203.sql'), $sql);
        // var_dump(count($datas));
        var_dump(time() - $e);
    }

    public function parse_logs3()
    {
        $e = time();
        $filename = public_path('dist/a-access-2010211248.log');
        $handle = @fopen($filename, 'r');
        $datas = [];
        $string = '';
        $i = 1;
        $sql = '';
        $ins = "INSERT INTO xpel_logs_1022 (`ip`, `time`, `method`, `url`, `status`, `referer`, `agent`, `log`, `created_at`, `updated_at`) VALUES \n";
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $item = $this->parseLogLine($buffer);
                $item['created_at'] = date('Y-m-d H:i:s');
                $item['updated_at'] = date('Y-m-d H:i:s');
                $s = array_values($item);
                $datas[] = $s;
                if (empty($i % 10000)) {
                    $string = json_encode($datas);
                    $search = ['[[', '[', ']]', ']'];
                    $repace = ['(', '(', ')', ')'];
                    $string = str_replace($search, $repace, $string);
                    $sql .= $ins.$string."; \n";
                    $datas = [];
                }
                $i++;
            }
            if (!feof($handle)) {}
            fclose($handle);
        }
        if (!empty($datas)) {
            $string = json_encode($datas);
            $search = ['[[', '[', ']]', ']'];
            $repace = ['(', '(', ')', ')'];
            $string = str_replace($search, $repace, $string);
            $sql .= $ins.$string."; \n";
        }
        file_put_contents(public_path('dist/xpel_logs_102203.sql'), $sql);
        // var_dump(count($datas));
        var_dump(time() - $e);
    }

    public function parse_logs2(Request $request)
    {
        $e = time();
        $filename = public_path('dist/a-access-2010211248.log');
        $handle = @fopen($filename, 'r');
        // $datas = [];
        $string = '';
        $i = 1;
        $ins = "INSERT INTO xpel_logs_1022 (`ip`, `time`, `method`, `url`, `status`, `referer`, `agent`, `log`, `created_at`, `updated_at`) VALUES \n";
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $item = $this->parseLogLine($buffer);
                $item['created_at'] = date('Y-m-d H:i:s');
                $item['updated_at'] = date('Y-m-d H:i:s');

                /*$s = implode("','", $item);
                // $s = str_replace("'", "\'", $s);
                $s = "('".$s."')";*/

                $s = json_encode(array_values($item));
                $s = str_replace('[', '(', $s);
                $s = str_replace(']', ')', $s);
                if ($i % 5000) {
                    if ($string) {
                        $string .= ",\n".$s;
                    } else {
                        $string = $ins.$s;
                    }
                } else {
                    $string .= ";\n".$ins.$s;
                }
                $i++;
            }
            if (!feof($handle)) {}
            fclose($handle);
        }
        file_put_contents(public_path('dist/xpel_logs_1022.sql'), $string);
        // var_dump(count($datas));
        var_dump(time() - $e);
    }

    public function parse_logs(Request $request)
    {
        return ;
        $filename = public_path('dist/a-access-2010211248.log');
        $handle = @fopen($filename, 'r');
        $datas = [];
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $item = $this->parseLogLine($buffer);
                $item['created_at'] = date('Y-m-d H:i:s');
                $item['updated_at'] = date('Y-m-d H:i:s');
                $datas[] = $item;
                if (count($datas) >= 5000) {
                    DB::table('xpel_logs_1021')->insert($datas);
                    $datas = [];
                }
            }
            if (!feof($handle)) {}
            fclose($handle);
        }
        if (count($datas) > 0) {
            DB::table('xpel_logs_1021')->insert($datas);
            $datas = [];
        }
        var_dump(count($datas));
    }
    public function route(Request $request, $a)
    {
        return $this->$a($request);
    }

    private $levels_imgs = [
        'debug' => 'info-circle',
        'info' => 'info-circle',
        'notice' => 'info-circle',
        'warning' => 'exclamation-triangle',
        'error' => 'exclamation-triangle',
        'critical' => 'exclamation-triangle',
        'alert' => 'exclamation-triangle',
        'emergency' => 'exclamation-triangle',
        'processed' => 'info-circle',
        'failed' => 'exclamation-triangle'
    ];
    private $patterns = [
        '/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}([\+-]\d{4})?)\](?:.*?(\w+)\.|.*?)',
        ': (.*?)( in .*?:[0-9]+)?$/i'
    ];
    private $level =['debug','info','notice','warning','error','critical','alert','emergency','processed','failed'];

    public function rand()
    {
        \Debugbar::disable();
        return Str::random(8);
    }
    public function dwz(Request $request, $a)
    {
        \Debugbar::disable();

        return [$a];

    }

    protected function mmc($m)
    {
        $ol = new Aalog();
        $ol->old =$m;
        $ol->save();
        return $ol;
    }

    public function db7()
    {

        $a = [];

        $model = Member::where('id', 3);
        $e = $model->first();
        $f = $model->first();

        $f = $e = Member::find(3);
        $ol = $this->mmc($e);
        $e = $e;
        $f->tkid = Str::random(16);
        $f->save();
        $ol->new =$f;
        $ol->save();


        return [$ol, $f->tkid];


        $l = ['debug','info','notice','warning','error','critical','alert','emergency','processed','failed'];

        $handle = @fopen(storage_path('logs/mysql/laravel-2020-09-25.log'), 'r');
        $datas = [];
        $key = 0;
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                // $p0 = '/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}([\+-]\d{4})?)\](?:.*?(\w+)\.|.*?)/';
                $p0 = '/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}([\+-]\d{4})?)\](?:.*?(\w+)\.|.*?)';
                $p1 = ': (.*?)( in .*?:[0-9]+)?$/i';
                $buffer = strtolower($buffer);
                $obj = [];
                $res = [];
                foreach (array_keys($this->levels_imgs) as $level) {
                    /*if (strpos(strtolower($buffer), '.' . $level)) {
                        $dd = preg_match( $p0.$level.$p1, $buffer, $data);
                        $obj = json_decode($data[4]);
                        return [$data, $dd, $obj ];
                    }*/
                    if (strpos(strtolower($buffer), '.' . $level)) {
                        $dd = preg_match( $this->patterns[0].$level.$this->patterns[1], $buffer, $data);
                        $obj = json_decode($data[4]);
                        $res = [
                            'user_id' => @$obj->user_id,
                            'user_type' => @$obj->user_type,
                            'user_name' => @$obj->user_name,
                            'ip' => @$obj->ip,
                            'method' => @$obj->method,
                            'url' => @$obj->url,
                            'table_name' => @$obj->table_name,
                            'operation' => @$obj->operation,
                            'query' => @json_encode(@$obj->query) ?: null,
                            'created_at' => $data[1],
                            'updated_at' => date('Y-m-d H:i:s'),
                        ];
                    }
                }
                return $res;
                    $level = array_keys($this->levels_imgs);
                // $dd = preg_split( $p0, $buffer);


                $obj = json_decode($buffer);
                return $buffer;
                return [$obj];

                if ($key < 3) {
                    $key++;
                    // continue;
                }
                $datas[] = $buffer;
            }

            if (!feof($handle)) {
                echo "Error: unexpected fgets() fail\n";
            }
            fclose($handle);
        }
        return $datas;
        return [array_keys($datas)];


        $roll_number = '2707359';
        $data = DB::table('b_json')->where('coverages', 'like', '%"roll_number": "'.$roll_number.'"%')->get();
        $coverages = Arr::pluck($data, 'coverages');
        $length = 0;
        foreach ($coverages as $k => $v) {
            $a = json_decode($v);
            foreach ($a as $l) {
                if ($l->roll_number == $roll_number) {
                    $length += $l->length;
                }
            }
        }
        $datas = DB::table('b_json')->paginate(50);
        $datas = makeWRL($datas);

        return view('xpel.list', [
            'data' => $datas,
        ]);

        return [getWLen(3), getWRLen($roll_number), $length, $coverages];
        return $data;


        $e = memory_get_usage();
        echo "初始: ".$e."B\n";
        $length = 0;

        // 'coverages->length'
        // $data = DB::table('a_json')->where('coverages->7011296', '!=', null);
        $data = DB::table('a_json')
            // ->select(DB::raw("JSON_EXTRACT(coverages, '$[*].length') as length"))
            ->get();
        $u = [];
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
            // $len = @json_decode($v->length) ?: [];
            // $length += array_sum($len) ?: 0;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $data = DB::table('a_json')->get();
        foreach ($data as $k => $v) {
            $item = $v;
            $u[] = (array)$item;
        }
        $d = memory_get_usage();
        echo "使用: ".memory_get_usage()."B\n";
        echo "使用: ".($d / 1024 / 1024)."MB\n";
        echo "使用: ".(($d-$e) / 1024 / 1024)."MB\n";
        return [$length];

    }

    public function db6()
    {
        $offset = DB::table('b_json')->count();
        $total = DB::table('warranty')->count();
        if ($offset >= $total) {
            return [];
        }
        $data = DB::table('warranty')
            ->offset($offset)
            ->limit(5000)
            ->get(['warranty_id', 'user_id', 'first_name', 'address', 'region_id', 'pre_id']);
        $ids = Arr::pluck($data, 'warranty_id');
        $coverages = DB::table('warranty_to_vehicle_coverage')->whereIn('warranty_id', $ids)
            ->get();
        $dc = [];
        foreach ($coverages as $k => $v) {
            if (empty($dc[$v->warranty_id])) {
                $dc[$v->warranty_id] = [];
            }
            $dc[$v->warranty_id][] = [
                'part_id' => $v->part_id,
                'roll_number' => $v->roll_number,
                'length' => $v->length,
            ];
        }
        $uploads = [];
        foreach ($data as $k => $v) {
            $uploads[] = [
                'warranty_id' => $v->warranty_id,
                'user_id' => $v->user_id,
                'first_name' => $v->first_name,
                'address' => $v->address,
                'region_id' => $v->region_id,
                'pre_id' => $v->pre_id,
                'coverages' => @$dc[$v->warranty_id] ? @json_encode(@$dc[$v->warranty_id])  : null
            ];
        }
        DB::table('b_json')->insert($uploads);
        redirect('/'.request()->path());

        return ['safd'];

    }

    public function db5()
    {
        DB::table('member')->insertGetId();
        // $a = Member::where('password', 'like', '%jzGiMvcgHblwxGt8%')->first();
        $a = DB::table('member as m')->where('id', 1)->get(['from']);
        $a = Member::where('password', 'like', '%jzGiMvcgHblwxGt8%')->first();
        $a->tkid = Str::random(8);
        $a->save();
        $a = new Member();
        $a->username = Str::random(8);
        $a->password = Str::random(16);
        $a->save();
        $a = Member::find(38);
        if ($a) {
            $a->delete();
        }
        foreach (range(0,6) as $v) {
            $u[] = [
                'username' => Str::random(8),
                'password' => Str::random(16),
                'created_at' => time(),
                'updated_at' => time(),
                'from' => Str::random(8),
            ];
        }
        DB::table('member')->insert($u);
        DB::table('member')->where('id', 24)->delete();
        DB::table('member')->where('password', 'like', Str::random(8))->delete();
    }

    public function db4()
    {
        DB::connection()->enableQueryLog();
        // DB::listen();
        /*$b = new Member();
        $c = $b->find(1);

        $a = new Member();
        $a->username = Str::random(8);
        $a->password = Str::random(16);*/
        // DB::connection()->enableQueryLog();

        /*foreach (range(1,50) as $v) {
            $a = new Member();
            $a->username = Str::random(8);
            $a->password = Str::random(16);
            $a->save();
        }*/

        $a = Member::where('password', 'like', '%jzGiMvcgHblwxGt8%');
        $data = $a->first();
        $data->tkid = Str::random(8);
        $data->save();
        $logs = DB::getQueryLog();
        return [$a->toSql(), $logs];
    }

    public function db3()
    {
        $user_id = 75;
        $roll_master = DB::connection('xpel')->table('roll_master');
        $wvc = DB::connection('xpel')->table('warranty_to_vehicle_coverage');

        $model = $roll_master->whereIn('roll_id', function ($query) use ($user_id) {
            $query->select('roll_sales.roll_id')->from('roll_sales')
                ->leftJoin('users', 'roll_sales.sold_to_user_id', '=', 'users.user_id')
                ->where('users.user_id', $user_id)
                ->orWhere('users.creator_id', $user_id)
                ->groupBy('roll_sales.roll_id');
        })
            ->leftJoin('warranty_to_vehicle_coverage', 'warranty_to_vehicle_coverage.roll_number', '=', 'roll_master.roll_number');

        return $model->paginate();
    }

    public function db2()
    {
        // return $this->db3();

        $user_id = 75;
        $roll_master = DB::connection('xpel')->table('roll_master');
        $wvc = DB::connection('xpel')->table('warranty_to_vehicle_coverage');

        // $model = $model->leftJoin('');

        $model = $roll_master->whereIn('roll_id', function ($query) use ($user_id) {
            $query->select('roll_sales.roll_id')->from('roll_sales')
                ->leftJoin('users', 'roll_sales.sold_to_user_id', '=', 'users.user_id')
                ->where('users.user_id', $user_id)
                ->orWhere('users.creator_id', $user_id)
                ->groupBy('roll_sales.roll_id');
        });
        $startdate = '2020-05-01';
        $enddate = '2020-08-31';
        $model = $model->where('date_entered', '>=', $startdate)
            ->where('date_entered', '<=', $enddate);
        $se = $model;
        $page = $model->paginate();
        $ids = Arr::pluck($page, 'roll_number');
        $m2 = $wvc->whereIn('roll_number', $ids)
            ->groupBy('roll_number')
            ->select(['roll_number', DB::raw('sum(length) as tlength')]);

        $m1 = $se->leftJoinSub($m2, 'wvc', 'wvc.roll_number', '=', 'roll_master.roll_number')
            ->select(['roll_master.*', 'tlength'])
            ->paginate();


        return $m1;
        return $model->toSql();
    }

    public function db1()
    {
        $val = '文字';
        $model = DB::table('abel_article');
        $model->where('id', '4');
        $model->where(function ($query) use ($val) {
            foreach (['title', 'classid', 'ttid'] as $c) {
               $query->orWhere($c, 'like', '%'.$val.'%');
            }
            /*$query->orWhere('title', 'like', '%'.$val.'%')
                ->orWhere('classid', 'like', '%'.$val.'%')
                ->orWhere('ttid', 'like', '%'.$val.'%');*/
        });

        return $model->toSql();
    }

    public function sms()
    {
        return [];
        $sid = "AC122c371792b240dbb50186bc86db00b9"; // Your Account SID from www.twilio.com/console
        $token = "d8828b056299b7b48507bf8627813581"; // Your Auth Token from www.twilio.com/console
        $client = new Services_Twilio($sid, $token);
        $number = '18817354090';
        $ext = '+86';
        $rno = random_int(0,9).random_int(0,9).random_int(0,9).random_int(0,9).random_int(0,9).random_int(0,9);
        $text = "验证码：{$rno}，有效时间5分钟。如果不是您本人操作，请忽略此信息。";

        $phone_number = $ext.$number;
        $phone_number = '+8618817354090';
        // $phone_number = preg_replace('/\s+/', '', $phone_number);
        $from_no = '+18558172636';

        # return $phone_number;

        $phone_number = '+8618817354090';
        // $bool = $client->messages('SMb3249f62ed884ab3804ed98aa3d8897b');
        $bool = $client->messages->create(
            $phone_number,
            [
                'from' => $from_no,
                'body' => $text
            ]
        );

        return $bool;

    }

    public function log(Request $request)
    {
        $file = storage_path('app/logs/'.date('Ymd').'.log');
        $logs = file_get_contents($file);
        $handle = @fopen($file, "r");
        $datas = [];
        if ($handle) {
            while (($buffer = fgets($handle)) !== false) {
                $datas[] = json_decode($buffer);
            }
            if (!feof($handle)) {
                // echo "Error: unexpected fgets() fail\n";
            }
            fclose($handle);
        }
        return $datas;

        die();
        $data = [
            'ip' => '123',
            'get' => 'get',
            'post' => ['123'],
            'timestamp' => time(),
            'datetime' => date('Y-m-d H:i:s'),
        ];
        $content = json_encode($data);
        $path = storage_path('app/logs/');
        if (!file_exists($path)) {
            mkdir($path);
        }
        file_put_contents($file, $content."\n",FILE_APPEND);
    }

    public function warranty_t1(Request $request)
    {
        return view('test');
    }

    public function warranty_t2(Request $request)
    {
        return redirect()->back()->withInput();
        return $request->input();
    }

    public function warrantyExcel(Request $request)
    {
        $file = public_path('template/20200916.xlsx');
        # \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("demo.xlsx");
        # $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        # $reader = new \PhpOffice\PhpSpreadsheet\IOFactory();
        # $reader = IOFactory::createReader($file);
        /*$reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $cell = $sheet->getCell('A1');*/
        # var_dump($spreadsheet);
       /*
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);*/
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
        $cellValue = $spreadsheet->getActiveSheet()->getCell('G11')->getCalculatedValue();
        $url = $spreadsheet->getActiveSheet()->getCell('G11')->getHyperlink()->getUrl();
        # zip_entry_open();
        $info = [];
        $filename = public_path('template/test2.zip');
        if (!file_exists($filename)) {
            file_put_contents($filename, file_get_contents($url));
        }
        $info = mime_content_type($filename);
        $filenames = [];
        if ($info == 'application/zip') {
            $zip = zip_open($filename);
            while ($zip_entry = zip_read($zip)) {
                if (zip_entry_open($zip, $zip_entry, "r")) {
                    $file_name = zip_entry_name($zip_entry);
                    if (in_array($file_name, ['0.jpg', '1.jpg', '2.jpg', '3.jpg', '4.jpg'])) {
                        $content = zip_entry_read($zip_entry,zip_entry_filesize($zip_entry));
                        $pf = pathinfo($file_name);
                        $k = $pf['filename'];
                        $filepath = public_path('upload/'.date('Ym').'/');
                        if (!file_exists($filepath)) {
                            mkdir($filepath);
                        }
                        $file = Str::random(16).'.jpg';
                        $filenames[$k] = $file;
                        file_put_contents($filepath . $file, $content);
                    }
                }
                zip_entry_close($zip_entry);
            }
        }
        return [$filenames, $filenames[1], $filenames[2], $filenames[3], $filenames[0]];
    }
    function get_zip_originalsize($filename, $path) {
        //先判断待解压的文件是否存在
        if(!file_exists($filename)){
            die("文件 $filename 不存在！");
        }
        $starttime = explode(' ',microtime()); //解压开始的时间

        //将文件名和路径转成windows系统默认的gb2312编码，否则将会读取不到
        $filename = iconv("utf-8","gb2312",$filename);
        $path = iconv("utf-8","gb2312",$path);
        //打开压缩包
        $resource = zip_open($filename);
        $i = 1;
        //遍历读取压缩包里面的一个个文件
        while ($dir_resource = zip_read($resource)) {
            //如果能打开则继续
            if (zip_entry_open($resource,$dir_resource)) {
                //获取当前项目的名称,即压缩包里面当前对应的文件名
                $file_name = $path.zip_entry_name($dir_resource);
                //以最后一个“/”分割,再用字符串截取出路径部分
                $file_path = substr($file_name,0,strrpos($file_name, "/"));
                //如果路径不存在，则创建一个目录，true表示可以创建多级目录
                if(!is_dir($file_path)){
                    mkdir($file_path,0777,true);
                }
                //如果不是目录，则写入文件
                if(!is_dir($file_name)){
                    //读取这个文件
                    $file_size = zip_entry_filesize($dir_resource);
                    //最大读取6M，如果文件过大，跳过解压，继续下一个
                    if($file_size<(1024*1024*30)){
                        $file_content = zip_entry_read($dir_resource,$file_size);
                        file_put_contents($file_name,$file_content);
                    }else{
                        echo "<p> ".$i++." 此文件已被跳过，原因：文件过大， -> ".iconv("gb2312","utf-8",$file_name)." </p>";
                    }
                }
                //关闭当前
                zip_entry_close($dir_resource);
            }
        }
        //关闭压缩包
        zip_close($resource);
        $endtime = explode(' ',microtime()); //解压结束的时间
        $thistime = $endtime[0]+$endtime[1]-($starttime[0]+$starttime[1]);
        $thistime = round($thistime,3); //保留3为小数
        echo "<p>解压完毕！，本次解压花费：$thistime 秒。</p>";
    }

    public function page(Request $request)
    {

        $json = file_get_contents(storage_path('app/static/toefl2020.json'));
        $obj = json_decode($json, 1);
        $data = $obj['centerList'];
        $centerCodeList = $obj['centerCodeList'];

        $pct = [];
        foreach ($centerCodeList as $k => $v) {

            $key = $v['provinceCn'];
            if (empty($pct[$v['provinceCn']])) {
                $pct[$v['provinceCn']] = [];
            }
            if (empty($pct[$v['provinceCn']][$v['cityCn']])) {
                $pct[$v['provinceCn']][$v['cityCn']] = 1;
            }
        }
        $pct2 = [];
        foreach ($pct as $k => $v) {
            $pct2[$k] = array_keys($v);
        }
        $pct = $pct2;
        return $pct;
        return [];


        $currentPage = LengthAwarePaginator::resolveCurrentPage();
        $json = file_get_contents(storage_path('app/static/tofel1.json'));
        $data = json_decode($json, 1);

        $kaoshiriqi = [];
        $chengshi = [];
        foreach ($data as $k => $v) {
            $c0 = trim($v['chengshi']);
            $c1 = trim($v['riqi']);
            if (empty($chengshi[$c0])) {
                $chengshi[$c0] = 1;
            }
            if (empty($kaoshiriqi[$c1])) {
                $kaoshiriqi[$c1] = 1;
            }
        }

        return [];

        $collection = new Collection($data);
        # $res = $collection->paginate(15);
        $perPage = 15;
        $currentPageSearchResults = $collection->slice($currentPage * $perPage, $perPage)->all();
        $paginatedSearchResults= new LengthAwarePaginator($currentPageSearchResults, count($collection), $perPage);

        return view('2020.NBf6TOo8', [
            'data' => $paginatedSearchResults
        ]);
        return [$currentPage];
        #
    }

    public function search1(Request $request)
    {
        $s = 'BECI的';
        $s = trim($s);
        $k = mb_str_split($s);
        $ks = [];
        foreach ($k as $w) {
            if (trim($w)) {
                $ks[] = trim($w);
            }
        }
        $kw = implode('%', $ks);
        $kw = '%'.$kw.'%';

        $d = ArticleAbel::where('title', 'like', $kw)->limit(10)->get(['id', 'title']);

        $a1 = ArticleAbel::where('title', 'like', '%'.$s.'%')
            ->select(['id', 'title', 'id as l1', DB::raw('0 as l2')]);

        // return $a1->get();

        $a2 = ArticleAbel::where('title', 'like', $kw)
            ->select(['id', 'title', 'id as l2', DB::raw('0 as l1')])
            ->union($a1)
            ->orderByDesc('l1')
            ->orderByDesc('l2')
            ->limit(10)
            ->get();
        $kks = '%'.$s.'%';
        $a3 = ArticleAbel::where('title', 'like', $kw)
            ->where('title', 'not like', $kks)
            ->select(['id', 'title', DB::raw('id as l2, 0 as l1')]);

        $a4 = ArticleAbel::select(['id', 'title', DB::raw('id as l1, 0 as l2')])
            ->where('title', 'like', $kks)
            ->union($a3)
            ->orderByDesc('l1')
            ->orderByDesc('l2')
            ->limit(10)
            // ->dump();
            ->get();

        return $a4;



        $query = "SELECT id, title, id as l1, 0 as l2 FROM abel_article where title like $kks union select id, title, 0 as l1, id as l2 from abel_article where title like $kw and title not like $kks order by l1 desc, l2 desc";
        // DB::select();

        // return $a3->get();
        // return $a4;
        $a0 = $a1->get();
        // return [$a0, $s, $a2];
        // return [count($a2), $a2];
        // return $a1->get();
        DB::table('d')->dump();
        // ArticleAbel::where('id', 3)->where


    }

    public function rands()
    {
        foreach (range(0,50) as $v) {
            $data[] = Str::random(8);
        }

        return $data;
    }

    public function dompdf(Request $request)
    {
        $html = '<div class="text"><p>菲律宾马尼拉-在卫生部（DOH）记录了另外2110例病例后，该国的新型冠状病毒肺炎总感染人数在上周日（2020年7月26日）突破了8万大关。</p><p>卫生部数据显示，菲律宾目前累计已有80448例COVID-19病例，其中现有病例为52406例。</p><p>此外，卫生部记录了382例新增康复病例和39例新增死亡病例，该国的康复总人数达到26110人，死亡总人数达到1932人。</p><p class="text-center"><img src="https://s1.baguio.top/202007/2020073010371312771.jpg" alt="菲律宾疫情加重，短时间内怕是没办法恢复实地游学"></p><p>大多数人会在感染新型冠状病毒之后出现轻度或中度症状，例如发烧和咳嗽。但是，对于老年人和有潜在健康问题的人，可能会引起更严重的疾病，包括肺炎和死亡。</p><p>菲律宾现有新冠病例中，轻症感染者者占89.83%，无症状感染者占9.25%，重症感染者占0.49%，危重症感染者占0.42%。</p><p class="text-center"><img src="https://s1.baguio.top/202007/2020073010371312772.jpg" alt="菲律宾疫情加重，短时间内怕是没办法恢复实地游学"></p><p>近期，菲律宾感染人数居高不下，连续4天单日新增病例超过2000例。这与放松校区隔离管制后人员接触增多有着密切关系。</p><p>看来目前短时间内无法恢复实地游学了</p><p>爱学习的宝宝们已经开始在线上跟老师学习英语了！</p><p><a href="http://www.nanqi.org/u/DBVyvbSl" target="_blank" class="text-primary">2020暑期密集在线英语课程免费试听</a></p></div>';
        $dompdf = new Dompdf();
        $dompdf->loadHtml($html);
        $dompdf->setPaper('A4');
        $dompdf->render();
        return response()->stream($dompdf->stream());
        return response()->file($dompdf->stream());
    }

    public function mpdf(Request $request)
    {
        return $this->ex1($request);

        $text = file_get_contents(storage_path('app/template/pdf1.php'));
        $search = [
            '<!-- temp1 -->',
            '<!-- temp2 -->',
            '<!-- temp3 -->',
            '<!-- temp4 -->',
            '<!-- temp5 -->',
            '<!-- temp6 -->',
        ];
        $replace = [
            date('Y-m-d'),
            '学员姓名',
            date('Y-m-d', time()-rand(3600, 86400*7)),
            '40分钟',
            rand(10, 40),
            '<!-- temp6 -->',
        ];
        $html = str_replace($search, $replace, $text);
        $classInfo = rand(1,6);
        $r_text = '';
        foreach (range(1,6) as $k => $v) {
            if ($v <= $classInfo) {
                $r_text .= '<tr><td>'.$v.'</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>';
            } else {
                $r_text .= '<tr><td>&nbsp;</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>';
            }
        }
        $r_text .= '<tr><td>&nbsp;</td><td></td><td></td><td></td><td></td><td>总数</td><td><b>'.array_sum(range(1,$classInfo)).'</b></td></tr>';
        $html = str_replace('<!-- temp101 -->', $r_text, $html);

        $mpdf = new Mpdf([
            'default_font' => 'Microsoft YaHei'
        ]);
        $watermarkImage = storage_path('app/public/2020041001272698731.png');
        $mpdf->SetWatermarkImage ($watermarkImage);
        $mpdf->showWatermarkImage = true;
        $mpdf->autoScriptToLang = true;
        $mpdf->autoLangToFont = true;
        $mpdf->WriteHTML($html);
        foreach (range(1,100) as $v) {
            $path = storage_path('app/'.date('Ymd').'/');
            $mpdf->Output($path.Str::random(16).'.pdf', 'F');
        }
        return $v;
        // return response()->stream($mpdf->Output());
    }

    public function ex1(Request $request)
    {
        $ot = new OnlineTestController();
        $text = file_get_contents(storage_path('app/template/exam1.php'));
        $data = $ot->makePaper($request, 30);
        $st = $data['data'];
        $answers = $data['answer'];
        $string = '';
        $answer = '';
        foreach ($st as $k => $v) {
            $i = $k+1;
            $s = '';
            $s .= '<div class="item mt20">';
            $s .= '<h5 class="text"><b>'.$i.'</b>. <span>'.$v['text'].'</span></h5>';
            $s .= '<div class="pl20">';
            foreach ($v['choice'] as $o) {
                $s .= '<div class="choice">'.$o['value'].'. '.$o['text'].'</div>';
            }
            $s .= '</div>';
            $s .= '</div>';
            $string .= $s;
        }
        foreach ($answers as $k => $v) {
            $i = $k+1;
            $sa = '<div class="item">'.$i.'. '.$v.'</div>';
            $answer .= $sa;
        }
        $html = str_replace('<!-- UNIQUE_ID -->', Str::random(32), $text);
        $html = str_replace('<!-- EXAM_ITEM -->', $string, $html);
        $html = str_replace('<!-- EXAM_ANSWER -->', $answer, $html);
        return $html;
    }

    /*
     * 试题转为PDF
     * */

    public function exam2pdf(Request $request)
    {

        return $this->makeExamFile200($request);

        return $this->forWenku($request);

        $id = (int) $request->input('id');

        $t1 = microtime(1) * 10000;
        $this->newExamFile1($request);
        // $this->makeExamFile200($request);
        $t2 = microtime(1) * 10000;

        if ($id < 100) {
            $id = $id+1;
            return response(($t2-$t1).'<script>setTimeout(() => {window.location.assign("?id='.$id.'")}, 3000)</script>');
        }
        return [$t2-$t1];
    }

    public function forWenku(Request $request)
    {
        $t1 = time();
        $ot = new OnlineTestController();
        $text = file_get_contents(storage_path('app/template/exam2.php'));
        foreach (range(21,40) as $index) {
            $data = $ot->makePaper($request, 30);
            $st = $data['data'];
            $answers = $data['answer'];
            $string = '';
            $answer = '';
            foreach ($st as $k => $v) {
                $i = $k+1;
                $s = '';
                $s .= '<div class="item mt20">';
                $s .= '<h5 class="text"><b>'.$i.'</b>. <span>'.$v['text'].'</span></h5>';
                $s .= '<div class="pl20">';
                foreach ($v['choice'] as $o) {
                    $s .= '<div class="choice">'.$o['value'].'. '.$o['text'].'</div>';
                }
                $s .= '</div>';
                $s .= '</div>';
                $string .= $s;
            }
            foreach ($answers as $k => $v) {
                $i = $k+1;
                $sa = '<div class="item">'.$i.'. '.$v.'</div>';
                $answer .= $sa;
            }
            $html = str_replace('<!-- UNIQUE_ID -->', Str::random(32), $text);
            $html = str_replace('<!-- EXAM_ITEM -->', $string, $html);
            $html = str_replace('<!-- EXAM_ANSWER -->', $answer, $html);
            $mpdf = new Mpdf([]);
            $watermarkImage = storage_path('app/public/2020041001272698731.png');
            $mpdf->SetWatermarkImage ($watermarkImage);
            $mpdf->showWatermarkImage = true;
            $mpdf->autoScriptToLang = true;
            $mpdf->autoLangToFont = true;
            $mpdf->useAdobeCJK = true;
            $mpdf->WriteHTML($html);
            $date = '20200701';
            $path = storage_path('app/'.date('Ymd', strtotime($date)).'/');
            if (!file_exists($path)) {
                mkdir($path);
            }
            $name = str_pad($index, 5, 0, 0);
            $mpdf->Output($path.'在线英语测试试题及答案-A'.$name.'.pdf', 'F');
        }
        $t2 = time();
        return [$t2-$t1];
    }

    public function newExamFile1(Request $request)
    {
        $ot = new OnlineTestController();
        $data = [];
        foreach (range(0, 2000) as $v) {
            $text = $ot->makePaper($request, 30);
            $data[] = [
                'text' => json_encode($text),
                'num' => 30
            ];
        }
        $b = DB::table('online_paper_1')->insert($data);
        return [$b];
    }

    public function makeExamFile200(Request $request)
    {
        $t1 = time();
        $ot = new OnlineTestController();
        $text = file_get_contents(storage_path('app/template/exam1.php'));
        foreach (range(1331,2000) as $index) {
            $data = $ot->makePaper($request, 30);
            $st = $data['data'];
            $answers = $data['answer'];
            $string = '';
            $answer = '';
            foreach ($st as $k => $v) {
                $i = $k+1;
                $s = '';
                $s .= '<div class="item mt20">';
                $s .= '<h5 class="text"><b>'.$i.'</b>. <span>'.$v['text'].'</span></h5>';
                $s .= '<div class="pl20">';
                foreach ($v['choice'] as $o) {
                    $s .= '<div class="choice">'.$o['value'].'. '.$o['text'].'</div>';
                }
                $s .= '</div>';
                $s .= '</div>';
                $string .= $s;
            }
            foreach ($answers as $k => $v) {
                $i = $k+1;
                $sa = '<div class="item">'.$i.'. '.$v.'</div>';
                $answer .= $sa;
            }
            $html = str_replace('<!-- UNIQUE_ID -->', Str::random(32), $text);
            $html = str_replace('<!-- EXAM_ITEM -->', $string, $html);
            $html = str_replace('<!-- EXAM_ANSWER -->', $answer, $html);
            $mpdf = new Mpdf([]);
            $watermarkImage = storage_path('app/public/2020041001272698731.png');
            $mpdf->SetWatermarkImage ($watermarkImage);
            $mpdf->showWatermarkImage = true;
            $mpdf->autoScriptToLang = true;
            $mpdf->autoLangToFont = true;
            $mpdf->useAdobeCJK = true;
            $mpdf->WriteHTML($html);
            $date = '20200702';
            $path = storage_path('app/'.date('Ymd', strtotime($date)).'/');
            if (!file_exists($path)) {
                mkdir($path);
            }
            $name = str_pad($index, 5, 0, 0);
            $mpdf->Output($path.'在线英语测试试题及答案-A'.$name.'.pdf', 'F');
        }
        $t2 = time();
        return [$t2-$t1];
    }

    public function makeExamFile(Request $request)
    {
        $ot = new OnlineTestController();
        $text = file_get_contents(storage_path('app/template/exam1.php'));
        $index = 1;
        $data = $ot->makePaper($request);
        $st = $data['data'];
        $answers = $data['answer'];
        $string = '';
        $answer = '';
        foreach ($st as $k => $v) {
            $i = $k+1;
            $s = '';
            $s .= '<div class="item mt20">';
            $s .= '<h5 class="text"><b>'.$i.'</b>. <span>'.$v['text'].'</span></h5>';
            $s .= '<div class="pl20">';
            foreach ($v['choice'] as $o) {
                $s .= '<div class="choice">'.$o['value'].'. '.$o['text'].'</div>';
            }
            $s .= '</div>';
            $s .= '</div>';
            $string .= $s;
        }
        foreach ($answers as $k => $v) {
            $i = $k+1;
            $sa = '<div class="item">'.$i.'. '.$v.'</div>';
            $answer .= $sa;
        }
        $html = str_replace('<!-- UNIQUE_ID -->', Str::random(32), $text);
        $html = str_replace('<!-- EXAM_ITEM -->', $string, $html);
        $html = str_replace('<!-- EXAM_ANSWER -->', $answer, $html);
        $mpdf = new Mpdf([]);
        $watermarkImage = storage_path('app/public/2020041001272698731.png');
        $mpdf->SetWatermarkImage ($watermarkImage);
        $mpdf->showWatermarkImage = true;
        $mpdf->autoScriptToLang = true;
        $mpdf->autoLangToFont = true;
        $mpdf->useAdobeCJK = true;
        $mpdf->WriteHTML($html);

        $path = storage_path('app/'.date('Ymd').'/');
        $mpdf->Output($path.'在线英语测试题及答案（2020-07-10)）'.$index.'.pdf', 'F');
    }


    public function exam2pdfRaw(Request $request)
    {

        $ot = new OnlineTestController();

        $data = $ot->makePaper($request);
        $text = file_get_contents(storage_path('app/template/exam1.php'));
        $st = $data['data'];
        $answers = $data['answer'];
        $string = '';
        $answer = '';
        foreach ($st as $k => $v) {
            $i = $k+1;
            $s = '';
            $s .= '<div class="item mt20">';
            $s .= '<h5 class="text"><b>'.$i.'</b>. <span>'.$v['text'].'</span></h5>';
            $s .= '<div class="pl20">';
            foreach ($v['choice'] as $o) {
                $s .= '<div class="choice">'.$o['value'].'. '.$o['text'].'</div>';
            }
            $s .= '</div>';
            $s .= '</div>';
            $string .= $s;
        }
        foreach ($answers as $k => $v) {
            $i = $k+1;
            $sa = '<div class="item">'.$i.'. '.$v.'</div>';
            $answer .= $sa;
        }
        $html = str_replace('<!-- UNIQUE_ID -->', Str::random(32), $text);
        $html = str_replace('<!-- EXAM_ITEM -->', $string, $html);
        $html = str_replace('<!-- EXAM_ANSWER -->', $answer, $html);
        $mpdf = new Mpdf([]);
        $watermarkImage = storage_path('app/public/2020041001272698731.png');
        $mpdf->SetWatermarkImage ($watermarkImage);
        $mpdf->showWatermarkImage = true;
        $mpdf->autoScriptToLang = true;
        $mpdf->autoLangToFont = true;
        $mpdf->useAdobeCJK = true;
        $mpdf->WriteHTML($html);

        foreach (range(1,150) as $v) {
            $path = storage_path('app/'.date('Ymd').'/');
            $mpdf->Output($path.'在线英语测试题及答案（'.date('Y-m-d').'）'.$v.'.pdf', 'F');
        }
        return [];

        return $mpdf->Output();

    }



    public function pdf(Request $request)
    {
        $file = public_path('assets/中文版.pptx');

        $img = Image::make($file);
        return [$img];
    }

    public function index(Request $request)
    {
        $p = $request->hasHeader('origin');
        if (isset($p)) {
            $origin = $request->header('origin');
            $pathinfo = pathinfo($origin);
            $parse_url = parse_url($origin);
        }
        return [$origin, $pathinfo, $parse_url, env('host')];
        // HTTP_HOST
        // 'HTTP_ORIGIN'
        return [$request->server(), $request->header(), $request->all()];
    }

    public function whpj(Request $request)
    {
        $filename = base_path().'/storage/o/'.'whpj'.date('Y-m-d');
        if (!file_exists($filename)) {
            $url = 'https://www.boc.cn/sourcedb/whpj/';
            $content = file_get_contents($url);
            file_put_contents($filename, $content);
        }
        $content = file_get_contents($filename);

        return $content;



        $url = 'https://www.boc.cn/sourcedb/whpj/';
        $client = new Client();
        $data = $client->request('GET', 'https://api.github.com/repos/guzzle/guzzle');
        return [$data];
    }

    public function img(Request $request)
    {
        $exif = ["jpg", "png", "gif", "tif", "bmp", "ico", "psd", "webp", "data-url"];
        // return [storage_path('app/fonts/Helvetica.ttf'), base_path()];
        $text = date('Y-m-d');
        $img = Image::canvas(600,400, '#ffffff');
        $img->text($text, 300, 200, function ($font) {
            // $font->file(base_path().'/storage/fonts/Helvetica.ttf');
            $font->file(base_path().'/storage/fonts/NotoSansCJK-Light.otf');
            $font->size(64);
            $font->color('#ff0000');
            $font->align('center');
            $font->valign('center');
            // $font->color(array(255, 0, 0, 1));
        });
        return $img->response($exif[2]);
        return $img->encode('png');
    }


    public function view(Request $request)
    {

        return view('b.test');
        // HTTP_HOST
        // 'HTTP_ORIGIN'
        return [$request->server(), $request->header(), $request->all()];
    }
}
